Few words about: LAG and LEAD TSQL functions

In SQL SERVER 2012 we’ve got some new functions that can be useful in our daily work, today I would like to present my two favorite ones: LAG and LEAD.

LAG function is working only with OVER( ORDER BY) clause and access data from previous row. LEAD functions works in very similar way – it’s return data from next row. I hope next example will be good way to understand how it works.

First of all we’ve got very simple example based on AdventureWorks2012DW database that you can download from codeplex.

SELECT

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear,

sum(f.SalesAmount) as SalesAmount FROM

[dbo].[FactResellerSales] f

JOIN [dbo].[DimDate] d

ON d.FullDateAlternateKey=f.OrderDate

JOIN DimReseller r

ON r.ResellerKey=f.ResellerKey

where f.ResellerKey=2

GROUP BY

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear

ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear

This query returns sum of SalesAmount for the company with key value equal to grouped by year, month and its company name. As a result we have got 8 rows with data – very clear and simple.

lag function result

But what if we need to get sum of SalesAmount from the previous row? We need to use LAG function:

SELECT

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear,

sum(f.SalesAmount) as SalesAmount,

LAG(sum(f.SalesAmount)) over(ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear)

FROM

[dbo].[FactResellerSales] f

JOIN [dbo].[DimDate] d

ON d.FullDateAlternateKey=f.OrderDate

JOIN DimReseller r

ON r.ResellerKey=f.ResellerKey

where f.ResellerKey=2

GROUP BY

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear

ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear

LAG function result

Perfect! We’ve got result that was very hard to achieve in previous versions of SQL Server but now we need only to know how to use LAG functions. LEAD function works in the exactly the same. This easy example is not everything, we can also provide offset parameter to access data from previous rows:

SELECT

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear,

sum(f.SalesAmount) as SalesAmount,

LAG(sum(f.SalesAmount),2) over(ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear) as SalesAmountOffset2

FROM

[dbo].[FactResellerSales] f

JOIN [dbo].[DimDate] d

ON d.FullDateAlternateKey=f.OrderDate

JOIN DimReseller r

ON r.ResellerKey=f.ResellerKey

where f.ResellerKey=2

GROUP BY

r.ResellerName,

d.CalendarYear,

d.MonthNumberOfYear

ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear

And the result:

LAG functions reulst

Pretty cool! If we don’t want to show NULL values we can of course use ISNULL or COALESCE functions but the best way to deal with it is to provide third parameter as following:

LAG(sum(f.SalesAmount),2,0) over(ORDER BY ResellerName,CalendarYear,d.MonthNumberOfYear)

 

And the result:

LAG LEAD function result

I hope you find it useful!

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s