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:
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
,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]
order by CustomerID
This is a sample result set of this script:
Here is complete books online for Lead and Lag functions: