9/23/2015

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!