This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to demonstrate one of new LAG() 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 LAG() function is used for accessing data from the Previous Row in the same result set.
For example, we have a different transaction date in our table and require to find the total number of days difference between current and previous transaction date. We can use a LAG() function to achieve this.
Below is a full demonstration on this:
The Syntax:
|
1 2 |
LAG (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 LAG():
|
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 NextDay ,LAG(TransactionDateTime,1,0) OVER(ORDER BY TransactionDateTime) AS PreviousDay FROM tbl_LEADDemo ) SELECT ID ,NextDay ,PreviousDay ,CASE WHEN PreviousDay = '1900-01-01 00:00:00.000' THEN 0 ELSE DATEDIFF(DAY,PreviousDay,NextDay) END AS TransactionDayInterval FROM cteLeadDemo GO |
The Result:
