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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.