In this post, I am going to demonstrate one of new 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 LAG() function to achieve this.
Below is a full demonstration of this.
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.
First, Create sample table and data:
CREATE TABLE tbl_LEADDemo
ID INTEGER IDENTITY(1,1)
INSERT INTO tbl_LEADDemo (TransactionDateTime)
Calculate day difference using LAG():
;WITH cteLeadDemo AS
,TransactionDateTime AS NextDay
,LAG(TransactionDateTime,1,0) OVER(ORDER BY TransactionDateTime) AS PreviousDay
WHEN PreviousDay = '1900-01-01 00:00:00.000'
END AS TransactionDayInterval
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.