LAG() and LEAD() are two of the new analytical functions that has been introduced in SQL Server 2012. 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 to itself . Let us quickly understand this with an example.
Let us create a database for the same and insert some values. Use the following code to achieve the same on a SQL Server 2012 instance
USE [Master] GO IF EXISTS(SELECT name FROM sys.databases WHERE name = 'LagAndLeadDemo') BEGIN DROP DATABASE LagAndLeadDemo; END GO CREATE DATABASE LagAndLeadDemo; GO USE LagAndLeadDemo GO CREATE TABLE LagLead ( [ProductId] int, [SalesQty] int, [Year] int ); insert into LagLead VALUES (10,1000,2005),(20,1215,2005),(30,3327,2005), (10,2000,2006),(20,2415,2006),(30,3429,2006), (10,3050,2007),(20,3216,2007),(30,3737,2007), (10,3026,2008),(20,1618,2008),(30,3452,2008), (10,2004,2009),(20,4315,2009),(30,5435,2009), (10,3040,2010),(20,4015,2010),(30,4343,2010), (10,3060,2011),(20,4345,2011),(30,3237,2011), (10,3075,2012),(20,4035,2012),(30,3093,2012), (10,3055,2013),(20,5012,2013),(30,3321,2013), (10,3059,2014),(20,6019,2014),(30,3245,2014), (10,3150,2015),(20,6815,2015),(30,4000,2015);
Now let us implement LAG with following query
select [ProductId], [SalesQty], Year, LAG([SalesQty]) OVER (ORDER BY Year) as SalesQtyLastYear from LagLead where [ProductId] = 10 order by Year;
Now if we observe the data of the column ‘SalesQtylastYear’ then we will find that it reflects the value that belongs to the previous row of the SalesQty column.
Now let us implement Lead with the following query
select [ProductId], [SalesQty], Year, LAG([SalesQty]) OVER (ORDER BY Year) as SalesQtyLastYear, Lead([SalesQty]) OVER (ORDER BY Year) as SalesQtyNextYear from LagLead where [ProductId] = 10 order by Year;
What we observe now is that the data of the column ‘SalesQtylastYear’ reflects the next value that belongs to the SalesQty column.
So in a nutshell this is what LAG and LEAD does. Fetches the previous row and next row values respectively. Let us now calculate the difference between last years sales quantity and current year’s sales quantity
select [ProductId], [SalesQty], Year, LAG([SalesQty]) OVER (ORDER BY Year) as LastYearRevenue, [SalesQty] - LAG([SalesQty]) OVER (ORDER BY Year) as SalesQtyChange from LagLead where [ProductId] = 20 order by Year;
Tagged: Analytical functions in SQL Server 2012, LAG function, LEAD function, New features in SQL Server 2012, New Functions in SQL Server 2012, SQL Server 2012
If u want todo it for all products:
select [ProductId], [SalesQty],
Year,
LAG([SalesQty]) OVER
(partition by ProductID ORDER BY Year) as SalesQtyLastYear,
Lead([SalesQty]) OVER
(partition by ProductID ORDER BY Year) as SalesQtyNextYear
from dbo.LagLead
order by [ProductId],Year;
LikeLike