Problems

I have a data set that describe Sales data of all categories. Now I need to build a query to compare Sales of a given year with previous year for each category. For example, I have a data set of SALES

Sales Example

Now, I need to build a report to analyze how much sales of previous year for  each category. For example, I need to know how much sales of Accessories category in 2011 and then compare with SALES in 2012 as below:

Previous Sales By Category

Solution

Prior 2012, it is quite complex for us to build a query that met our expectation. We often use a self-join mechanism to compare values in  the current row with the values in a previous rows. However, Microsoft introduced new functions to support this in SQL Server 2012 edition or higher.

LAG() : Accesses data from a previous row in the same result set without the use of a self-join in SQL Server 2012. LAG provides access to a row at a given physical offset that comes before the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a previous row.

LEAD() : Accesses data from a subsequent row in the same result set without the use of a self-join in SQL Server 2012. LEAD provides access to a row at a given physical offset that follows the current row. Use this analytic function in a SELECT statement to compare values in the current row with values in a following row.

LAG() Syntax

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


LEAD() Syntax

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

LAG() Implementation

In this example, I used AdventureWorks2014 database.

USE AdventureWorks2014
GO
;WITH SalesComparison
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 SalesComparison;

Step 1: create CTE to store total sales of each category per year

Step 2: apply LAG function within Category partition, and sort Year by Ascending

Execution

 

LAG_Final
LAG() function

LEAD() Implementation

Step 1: create CTE to store total sales of each category per year

Step 2: apply LEAD function within Category partition, and sort Year by Ascending

USE AdventureWorks2014
GO
;WITH SalesComparison
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 SalesComparison;

 

LEAD_Final
LEAD() function
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