LAG and LEAD functions in SQL Server 2012

In SQL server 2012, some new functions have been introduced as LAG() and LEAD() are two of the new analytical functions which are also called windows functions of SQL. These functions helps in accessing a value from a previous row (lag) and subsequent row (lead) in the same result set without joining the result set or a table to itself. We can use these functions to compare or do some aggregations between current and previous value or between current and next values

Here we have a Sales table, where we have Sales for each state as:

StateID	StateName	Sales
100	Delhi	25140
100	Delhi	28947
100	Delhi	32564
200	UP	65421
200	UP	15546
300	MP	84512
300	MP	64152
300	MP	84651
400	AP	41344

So for analytical purpose we want to see difference between each sale in respect of each state so for this we need to compare current sales amount with previous sales amount to get difference so there are two ways:

·         First we can use self join with Sales table to get previous sales amount with current sales amount for comparison

·         Second one is to use LEG() function without using self join as:

SELECT   [StateID], [StateName],

         LAG([Sales]) OVER (ORDER BY StateID) as PreviousSales

         ,Sales - LAG([Sales]) OVER (ORDER BY StateID) AS SalesDifference

from Sales_Data

order by StateID

Result is as:

StateID	StateName	Sales	PreviousSales	SalesDifference
100	Delhi	25140	0	25140
100	Delhi	28947	25140	3807
100	Delhi	32564	28947	3617
200	UP	65421	32564	32857
200	UP	15546	65421	-49875
300	MP	84512	15546	68966
300	MP	64152	84512	-20360
300	MP	84651	64152	20499
400	AP	41344	84651	-43307

In the above query you can see first we have use a LAG() function by using OVER clause with StateID to get previous sales amount for each row (result is coming under the PreviousSales column) and than we have substracted previous value with Sales amount to get difference between both current and previous Sales value.

In the same way if we want to get lead value (next row vlaue) than we can use LEAD() function in the same way as we did LAG() as:

select [StateID], [StateName],

LAG([Sales]) OVER (ORDER BY StateID) as PreviousSales,

Lead([Sales]) OVER (ORDER BY StateID) as NextSales

from Sales_Data

order by StateID

StateID	StateName	Sales	PreviousSales	NextSales
100	Delhi	25140	0	28947
100	Delhi	28947	25140	32564
100	Delhi	32564	28947	65421
200	UP	65421	32564	15546
200	UP	15546	65421	84512
300	MP	84512	15546	64152
300	MP	64152	84512	84651
300	MP	84651	64152	41344
400	AP	41344	84651	0

More on Lead() and Leg() function go through this link: SQL Servers Lag and Lead Functions

IF you have any thoughts or suggestions, please feel free to share or comment.
Thanks!