Introduction

In this article, I will show how to read data from the next row in one SELECT on the MSSQL 2008 and how it is easy to do that on MSSQL 2012 using one of the new 2012 functions.

Problem

Let's have the following TrainHistory table:

We want to calculate the number of days between the current row and the next row in one SELECT.

Generally a cursor is used to achieve this work, but it is time consuming for huge data. I show here how to resolve the problem in one select statement on MSSQL-2008 and MSSQL-2012.

On MSSQL-2008 using CTE

WITH    CTE_TH 
          AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY TrainID ORDER BY EventDate ) AS row_num 
                ,   TrainID
                ,   EventDate
                ,   Delay
                FROM TrainHistory 
             ) 
    SELECT T_CURRENT_ROW.* 
     ,   DATEDIFF(DAY, T_CURRENT_ROW.EventDate, T_NEXT_ROW.EventDate) AS days_without_delay 

 FROM CTE_TH T_CURRENT_ROW 
  LEFT JOIN CTE_TH T_NEXT_ROW 
            ON T_CURRENT_ROW.row_num + 1 = T_NEXT_ROW.row_num  
               AND T_CURRENT_ROW.TrainID = T_NEXT_ROW.TrainID

Results:

On MSSQL 2012 Using the New LEAD Function

The LEAD function gives access to retrieve column from previous row or the next row.

We obtain the same results without any join:

SELECT
       TrainID,
       EventDate,
       Delay,
       DATEDIFF(DAY, EventDate, LEAD(EventDate) OVER (PARTITION BY TraindID ORDER BY EventDate)) AS days_without_delay
FROM TrainHistory

History

2014-06-21 First release

Read Data from the Next Row