SQL Server 2012: Introduced LEAD function, the best alternative of Self Join to compare Next Row

In this post, I am going to demonstrate one of new 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 require to find the total number of days difference between current and next transaction date.
We can use LEAD() function to achieve this.

Below is a full demonstration of this.

The Syntax:

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:

Calculate day difference using LEAD():

The Result:

SQL Server LEAD Function

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of