Lag and Lead Functions in SQL Server 2012

SQL Server 2012 has a set of useful functions to work in T-SQL environment. Lead and Lag are one of the most useful functions introduced in 2012.

Lag function helps to access values from previous records, and Lead function helps to access values from next records in the data set.

structure of working with these functions is similar to each other. you would require to define the source column, offset, and the default value.

Lead([source column],[offset],[default value])

this means that Lead(OrderDate,1,null) will return the value in OrderDate column from the very next record’s  in the recordset, and if that record doesn’t exists it will return null (because of the default value configuration)

Lag function works exactly similar, you just need to set source column, offset, and default value.

Lag and Lead both are working with OVER Statement. OVER statement structure is similar to row_number() function.

OVER(Partition By …. Order By …) 

below shows an very simple example of using Lead and Lag functions to find previous and next order dates of each customer, and the duration between previous and current order, and also between current and next order in weeks.

here is the script:

select

CustomerID,OrderDate as [Current Order Date]

,lag(orderdate,1,null) over(partition by customerid order by orderdate) [Previous Order Date]

,datediff(week,lag(orderdate,1,null) over(partition by customerid order by orderdate),OrderDate) [Duration from Previous
Order]

,lead(orderdate,1,null) over(partition by customerid order by orderdate) [Next Order Date]

,datediff(week,orderdate,lead(orderdate,1,null) over(partition by customerid order by orderdate)) [Duration to Next Order]

from sales.SalesOrderHeader

order by CustomerID

This is a sample result set of this script: 

Here is complete books online for Lead and Lag functions:

Lead:

http://technet.microsoft.com/en-us/library/hh213125.aspx

Lag:

http://technet.microsoft.com/en-us/library/hh231256.aspx

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.