Click here to Skip to main content
Click here to Skip to main content
Go to top

Read Data from the Next Row

, 21 Jun 2014
Rate this:
Please Sign up or sign in to vote.
Read data from the next row

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

george b
Web Developer Bell Canada
Canada Canada
C,C++,C#,X++,.NET,JavaScript,ASP.NET,MVC,jQuery,Ajax,HTML5,WCF,JAVA,PHP,PowerShell,SharePoint 2010/2013,Dynamics AX,MSSQL,SQLCLR,MySQL,Oracle,Sybase, SSIS,SSRS,Infragistics,BI,CrystalReports,ADO.NET,EntityFramework,Unix,C Shell,PERL,COBOL,Z/OS.

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140926.1 | Last Updated 21 Jun 2014
Article Copyright 2014 by george b
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid