Lag and Lead Functions in SQL Server 2012

Posted by on Sep 10, 2013 in SQL Server, T-SQL | No Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">