How to Use Values from Previous or Next Rows in a SQL Server Query

Home > Blogs > SQL Server > How to Use Values from Previous or Next Rows in a SQL Server Query

How to Use Values from Previous or Next Rows in a SQL Server Query

1 11 Peter Avila
Added by May 28, 2013

I recently came across a problem that required having to perform a calculation in a query that involved a value in the current row and a value in the previous row.  The problem is that SQL queries perform operations on a row-by-row basis; accessing data on different rows at the same time requires the query to do some extra work.

In SQL Server versions prior to 2012, you need to perform a join using a row enumerator to match up rows with previous or next rows. In 2012 and higher, there are two functions, Lag() and Lead(), that greatly simplify the process.

I’ll describe the problem I ran into and show you how to implement a solution using both SQL Server versions 2005 – 2008 R2 and SQL Server 2012.

Set Up the Data

Before I get to the problem, here’s a script you can run to set up an environment for this example in case you want to follow along by doing. The script creates a database called UserManagement and then it creates and populates a table called UserActivity. When you’re done with this article, you can simply drop the UserManagement database to clean things up.

The Problem

To understand the problem, let’s look at the UserActivity table after running the script above. Notice that each row is either a logon or logoff activity and that, when shown in order by user and activity time, it’s logon, logoff, logon, logoff, and so on.


From this table we need to calculate the idle time per user per day—the time between a logon and the previous logoff for the same user on the same day. Take a moment to verify that, for user 1 on 1/1/2013 (the first 6 rows of the UserActivity table), there are two blocks of idle time; one is from 10:45 to 1:15 (2 ½ hr., or 150 min.), and the other is from 3:30 to 4:15 (45 min). Note that blocks of idle time that span two days will not be used.


To calculate the idle time, we’ll need to find the difference between the time in a logon row and the time in the previous row. That’s the obstacle to be surmounted, here. Remember, SQL queries perform operations on a row-by-row basis. This business of accessing previous or next rows requires some minor acrobatics in SQL Server 2008 R2 and previous versions. Let’s see how to do it.

A Solution Using SQLServer 2005, 2008 or 2008 R2

We’ll start by creating two derived tables—one holding only the logon rows in UserActivity and the other holding only the logoff rows. We’ll name them LogOns and LogOffs.


Next, we’ll need to join them so that a logon row is paired up with its logoff row. To match up a row with its previous row, we can enumerate the rows of the UserActivity table and include that enumeration in the two derived tables (rows in LogOn will all have odd row numbers and rows in LogOff will all have even row numbers). We can then join row x in LogOn to row x-1 in LogOff.

To make sure matched rows are for the same user and activity date, we will also include those two fields in the join condition when we write the query.


To implement this solution, we’ll use the Row_Number() function, the OVER() clause of a SELECT statement, a CTE and an inner join. I’ll show you the query now and then discuss it in detail below.


Here’s how it works:

  1. Find a CTE called UserActivityWRowNum in the query. This is just the UserActivity table with an additional Row_Number() column—the enumeration we need in order by User and ActivityTime. The Row_Number() function enumerates rows in the order specified in the ORDER BY clause in the OVER() clause.Here’s the data produced by the CTE with its RowNumber column:
  2. The CTE is used in the main query to create the two derived tables, LogOns and LogOffs.
  3. The join condition matches a logon time with the time of the previous logoff as long as the user and activity date are the same.

When we run the query, we can see the same two blocks of idle time for user 1 on 1/1/2013 that we saw at the very start:


That’s how you can do it in SQL Server 2005, 2008 or 2008 R2. If you’re using SQL Server 2012 or higher, it’s a little easier.

A Solution Using SQLServer 2012 or Higher

SQL Server 2012 introduced the new Lag() and Lead() functions that encapsulate all the logic of “reaching” back or forward respectively by any number of rows. We just need to tell it what value to retrieve from a previous row (either a column or an expression that references one or more columns), how many rows to go either back or forward, and the order of the rows or the “scale” over which it knows what a “previous” or “next” row is. We use parameters to tell it the first two. The order of the rows is specified using the OVER() clause with ORDER BY.

In the query below, the Lag() function is used to determine the idle time. It is the second argument of the DATEDIFF() function. We’re telling the Lag() function to retrieve the activity time from 1 row “back” using the same order of User and ActivityTime (the Lead() function is similar but returns values in rows that follow rather than precede the current row). Note the same blocks of idle time of 150 minutes and 45 minutes for user 1 on 1/1/2013.


We can tweak this to get the same results we got in the join solution we saw earlier. One way of doing that is shown in the query below where we create a CTE that puts corresponding values on the same row and then filters the data and calculates the idle time in the main query.



Have fun writing queries that access data in previous or next rows!

Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 608 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

See what people are saying...

  1. Avatar Salman farsi

    need some help plz , how do i update a column to next column column on same table with +1 increment. suppose i have a three row 1.00
    3.00 and i want to update these
    value just next column this format 2.00
    null .
    output :like
    column A column B
    1.00 2.00
    2.00 3.00
    3.00 4.00
    4.00 null

  2. Avatar Kathleen Wells

    The more I read, the greater your material is. I have covered lots of the other sources, like
    this on; however, only
    here, I’ve found valid information with such necessary facts to keep
    in mind. I suggest you will publish articles with many topics to update our knowledge, mine in particular.
    The language is another thing-just brilliant!
    I believe I have already found my perfect
    source of the most up-to-date info, thanks to you!

  3. Avatar Nitin

    I want to do quite the opposite. I have login and logout time. I want to create rows second by second repeating other column values from start of login time till logout time. Can you guide me?

  4. Peter Avila Peter Avila

    Hi, Adam. Did you read the entire article? You are right that SQL is not row-by-row based, but set based, and in that sense, the term row-by-row may have been a poor choice on my part, but I think that if you read the article you’ll see that I’m referring to the processing we can do in a query. If I want to subtract a value from one column from the value in another column in my query, that applies to one row at a time. Unless I use the approaches I describe in the article, it’s “row by row.”


  5. Avatar Jatin Mahesh Arya

    Thanks a lot; Your solution did help me resolve my encountered blocker issue.

  6. Avatar Brpatel

    Beautiful example and explanation. Never thought that SQL 2012 would simplify this task!

  7. Avatar Adam

    Over clause is part of quite a few functions that are called windowed functions. They’re extremely useful, but have two very, very severe limitations, which are good to be mentioned. One: they work only within the query dataset, so you won’t get anything outside it. It is, obviously, logical limitation – it’s called windowed function not without reason. Second: all windowed functions are row-based. Yes, you can define offset and range, but it’s always row based.

    Also, SQL is most definitely not a system that “operates on a row-by-row basis”. It’s set based, which is why it can very, very quickly process large datasets. Row-by-row is actually very inefficient way of doing things in SQL (any, not just SQL Server).

  8. Avatar Annie

    There is a key component missing. Notice that when the user change occurs from user 1 to user 2 the idle time is negative. That is due to the fact the Lag() is looking at the user 1 time and comparing it to user 2. Could you please change how to handle the user change and how to ‘reset’ the Lag() based on user.

  9. Avatar Amit

    I think you overthought on the solution:
    Instead the rownumber at the beginning, add Row Number to each table (after separating the LogOns and LogOffs, )
    and join the tables based on Row Number, User-Id, you dont need to join on dates (as this can change , if the user is log-off after 2 hours past 11PM)
    try it out, see if works..


  10. Avatar Damian Kuhne

    This is genius – Thank you very much.

    Very well explained also.

  11. Avatar shabeer

    Need some help please . How do i select the next record date in access sql. MY FIRST ROW DATE IS 01/06/2016.I want to use the next row date which is 01/06/2016 again as nextdate to do a datediff. I tried but it chooses the row with more than 01/06/2016. eg the third row date 08/06/2016.
    here is the sql i uses
    FROM (SELECT OrderID, AccNo, InvDate, Invoice, Nz((SELECT MIN(InvDate) FROM Rental T2 WHERE T1.accno = T2.accno AND T2.InvDate > T1.InvDate), DateSerial(YEAR(T1.InvDate), MONTH(T1.InvDate) + 1, 1)) AS NextDate FROM Rental AS T1) AS AllDays INNER JOIN Rental ON AllDays.OrderID=Rental.OrderID

Share your thoughts...

Please fill out the comment form below to post a reply.