This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing the demonstration on the new LEAD() function which is introduced by SQL Server 2012.
Generally, we are using Self Join to perform row-level analysis by comparing Next and Previous rows.
SQL Server 2012 has a LEAD() function, is used for accessing data from the Next Row in the same result set.
For example, we have a different transaction date in our table and need to find the total number of days difference between current and next transaction date. We can use a LEAD() function to accomplish this.
Below is a full demonstration on this:
The Syntax:
|
1 2 |
LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause ) |
The default OFFSET value is 1 means considering next immediate row and we can also change this OFFSET value. e.g. set OFFSET value of 4 means it accesses immediate fourth row.
Create a sample table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE tbl_LEADDemo ( ID INTEGER IDENTITY(1,1) ,TransactionDateTime DATETIME ) GO INSERT INTO tbl_LEADDemo (TransactionDateTime) VALUES ('2016-01-08 08:08:08') ,('2016-01-10 14:23:23') ,('2016-01-12 09:41:12') ,('2016-01-16 23:45:12') ,('2016-01-26 16:16:45') ,('2016-01-29 12:55:02') ,('2016-02-02 18:25:04') ,('2016-02-06 16:42:32') GO |
Calculate day difference using LEAD():
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
;WITH cteLeadDemo AS ( SELECT ID ,TransactionDateTime AS PreviousDay ,LEAD(TransactionDateTime,1,0) OVER(ORDER BY TransactionDateTime) AS NextDay FROM tbl_LEADDemo ) SELECT ID ,PreviousDay ,NextDay ,CASE WHEN DATEDIFF(DAY,PreviousDay,NextDay) < 0 THEN 0 ELSE DATEDIFF(DAY,PreviousDay,NextDay) END AS TransactionDayInterval FROM cteLeadDemo GO |
The Result:
