Problem:

A very common request is one in which someone wants to see a current time period metric in comparison to a previous time period metric such as by week, month, quarter, or year. In the past, this has been accomplished with a query in which a series of JOIN operations would have to be employed inside the query.

Issue:

Having to write queries with JOIN operations leads to large and complicated queries that can be very time consuming to complete.

Resolution:

SQL Server 2012 has many new functions including ‘LAG’ & ‘LEAD’. These two functions significantly simplify the query writing process and can, in some cases, remove the need for the JOIN operation.

By using the new LAG and LEAD functions, the amount of time that the query takes to write can be decreased dramatically.

Example:

Image 1 shows an example table being used to populate the chart. The table contains Sales, Units, and COGs by Product Line and Date for approximately a year.
 

Image 1

Image 2 shows the query on the table with the LAG function and the result set below that. Please notice in the highlighted areas in the image that the metrics PP Sales, PP Units, and PP COGs for Accessories Week 2 match the values CP Sales, CP Units, and CP COGs for Accessories Week 1. CP = Current Period & PP = Previous Period.
 

Image 2

Here is the query being used in the above example:

SELECT TOP 1000
SUM([Sale]) AS 'CP Sales',
SUM([Cost_of_Goods]) AS 'CP COGS',
SUM([Units]) AS 'CP Units',
Datepart(Week, Date) AS 'WeekNumber',
ProductLine,
LAG(SUM(Sale)) OVER(Order BY ProductLine, Datepart(Week, Date)) 'PP Sale',
LAG(SUM(Cost_of_Goods)) OVER(Order BY ProductLine, Datepart(Week, Date)) 'PP COGS',
LAG(SUM(Units)) OVER(Order BY ProductLine, Datepart(Week, Date)) 'PP Units'
FROM [dbo].[Sales_by_Date]
WHERE Date >= '1-1-2013'
GROUP BY Datepart(Week, Date), ProductLine
ORDER BY Datepart(Week, Date), ProductLine

Applies to:

• Private Cloud
• X Platform
• Enterprise
• Enterprise Suite