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