Lag and Lead

Overview

SQL Server 2012 introduces following analytical functions

• CUM_DIST & Percentile_DIST
• FIRST_Value & Last_Value
• LAG & LEAD
• Percent_Count & Percent_Rank

Out of these, LAG & LEAD is very useful in almost all applications, where we need to find previous and next value based on a specific result-set and we need to do the self-join, LAG & LEAD completely gets rid off this self join and makes it very easy.

Syntax

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Scenarios & Examples

• Compare values between years

Let's say we have TotalSales per year for each product and now I want to compare with previous year sales and next year sales. look at the below query and we can know how easy it is to derive using these functions, otherwise we need to do self-joins and it is always painful.

SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Order by SaleYear) PrevYearSales,

LEAD(TotalSales,1,0) OVER (Order by SaleYear) NextYearSales

FROM ProductSales

WHERE  ProductID = 2

ProductID	SaleYear	TotalSales	PrevYearSales	NextYearSales
2	2008	9000	0	96570
2	2009	96570	9000	67800
2	2010	67800	96570	78700
2	2011	78700	67800	90565
2	2012	90565	78700	0

0 is the default value, we will get 0 if function cannot derive anything. 

• Use LAG, LEAD along with GROUP By

Let's say, we want to find out aggregated value of TotalSales per year and we want to compare with previous and next year, then here is the query

SELECT SaleYear, SUM(TotalSales) TotalSales, LAG(SUM(TotalSales),1,0) OVER (Order by SaleYear) PrevYearSales,

LEAD(SUM(TotalSales),1,0) OVER (Order by SaleYear) NextYearSales

FROM ProductSales

group by SaleYear

SaleYear	TotalSales	PrevYearSales	NextYearSales
2008	28000	0	232070
2009	232070	28000	142600
2010	142600	232070	136500
2011	136500	142600	174465
2012	174465	136500	0

• Use LAG, LEAD within Partition

Let's say, we want to compare totalsales with previous and next year for all the products, then we can achieve this by integrating Partition by clause

SELECT ProductID, SaleYear, TotalSales, LAG(TotalSales,1,0) OVER (Partition By ProductID Order by SaleYear) PrevYearSales,

LEAD(TotalSales,1,0) OVER (Partition By ProductID Order by SaleYear) NextYearSales

FROM ProductSales

ProductID	SaleYear	TotalSales	PrevYearSales	NextYearSales
1	2008	10000	0	10400
1	2009	10400	10000	47000
1	2010	47000	10400	14500
1	2011	14500	47000	78000
1	2012	78000	14500	0
2	2008	9000	0	96570
2	2009	96570	9000	67800
2	2010	67800	96570	78700
2	2011	78700	67800	90565
2	2012	90565	78700	0
3	2008	8000	0	67800
3	2009	67800	8000	4500
3	2010	4500	67800	9000
3	2011	9000	4500	4500
3	2012	4500	9000	0
4	2008	1000	0	57300
4	2009	57300	1000	23300
4	2010	23300	57300	34300
4	2011	34300	23300	1400
4	2012	1400	34300	0