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.
Let's have the following
We want to calculate the number of days between the current row and the next row in one
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
AS ( SELECT ROW_NUMBER() OVER ( PARTITION BY TrainID ORDER BY EventDate ) AS row_num
, 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
On MSSQL 2012 Using the New LEAD Function
LEAD function gives access to retrieve column from previous row or the next row.
We obtain the same results without any join:
DATEDIFF(DAY, EventDate, LEAD(EventDate) OVER (PARTITION BY TraindID ORDER BY EventDate)) AS days_without_delay