Problem

Have you ever developed reports for tracking Year Over Year(YOY) Sales by Category from a OLTP database in SQL Server or something likes that?

To prior SQL Server 2012, it’s quite complicated for you to build T-SQL script to query data for these reports. Maybe, you must build many sub-queries to do that. However, Microsoft announced several new analytic functions from SQL Server 2012 and higher, which helps you do analytic tasks easier than. In this article, I’m introducing two of analytic functions LAG() and LEAD().

LAG()

Purpose

We use this function when we want to compare values in the current row to values in a previous row.

Syntax

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

scalar_expression

The value to be returned based on the specified offset. It is an expression of any type that returns a single (scalar) value. scalar_expression cannot be an analytic function.

offset

The number of rows back from the current row from which to obtain a value. If not specified, the default is 1.offset can be a column, subquery, or other expression that evaluates to a positive integer or can be implicitly converted to bigint. offset cannot be a negative value or an analytic function.

default

The value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned.default can be a column, subquery, or other expression, but it cannot be an analytic function. default must be type-compatible with scalar_expression.

OVER ( [ partition_by_clause ] order_by_clause )

partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the order of the data before the funnction is applied. If partition_by_clause is specified, it determines the order of the data in the partition. The order_by_clause is required.

(Source: https://msdn.microsoft.com/en-us/library/hh231256.aspx)

Example

In the first example, I want to compare total sales amount of all products of each year to its previous year.

USE AdventureWorks2014
GO
;WITH SalesYOY
AS
(SELECT YEAR(soh.OrderDate) AS [Year],SUM(sod.LineTotal) TotalSalesAmount
FROM Sales.SalesOrderHeader soh
 INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
 INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
 INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
 INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
GROUP BY YEAR(soh.OrderDate))
SELECT [Year], TotalSalesAmount,
LAG(TotalSalesAmount,1,0) OVER(ORDER BY [Year] ASC) AS PreviousSales
FROM SalesYOY;

And the result as below image

YOY Sales

In the second example,  I want to compare total sales amount of each category of each year to its previous year.

USE AdventureWorks2014 GO 
;WITH SalesYOY AS 
(SELECT YEAR(soh.OrderDate) AS [Year],pc.Name AS Category,SUM(sod.LineTotal) TotalSalesAmount 
FROM Sales.SalesOrderHeader soh 
INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID 
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID 
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID 
INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID GROUP BY YEAR(soh.OrderDate),pc.Name) 
SELECT [Year],Category,TotalSalesAmount, LAG(TotalSalesAmount,1,0) OVER(PARTITION BY Category ORDER BY [Year] ASC) AS PreviousSales FROM SalesYOY;

 

YOYByCategory

As the result, total sales amount of Accessories is 102439.820714 in 2012, and 20820.773108 in 2011.

LEAD()

Definition

Opposite to LAG() function, we use this function when we want to compare values in the current row to  a following row.

Syntax

LEAD ( scalar_expression [ ,offset ] , [ default ] ) 
    OVER ( [ partition_by_clause ] order_by_clause )

Example

Use LEAD() to compare total sales amount of each category of each year to total sales amount of next year.

USE AdventureWorks2014
GO
;WITH SalesYOY
AS
(SELECT YEAR(soh.OrderDate) AS [Year],pc.Name AS Category,SUM(sod.LineTotal) TotalSalesAmount
FROM Sales.SalesOrderHeader soh
 INNER JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
 INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
 INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
 INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
GROUP BY YEAR(soh.OrderDate),pc.Name)
SELECT [Year],Category,TotalSalesAmount,
LEAD(TotalSalesAmount,1,0) OVER(PARTITION BY Category ORDER BY [Year] ASC) AS NextSales
FROM SalesYOY;

And the result as below image. Total sales amount of Accessories is 20820.773108 in 2011 and 102439.820714 in 2012

YOY By LEAD

Conclusion

LAG() and LEAD() functions are useful functions to support us in analytic tasks. But you need to notice that they are only available in SQL Server 2012 and higher versions now.

 

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