How to calculate the increasing percentage of sales each month using T-SQL

Introduction

This T-SQL script will demo how to calculate the increasing percentage of sales each month in a year.

Scenarios

As some people asked how to calculate the increasing percentage of sales each month in a year. Sometimes there are several products in a table, we need find out the increasing percentage of a specified product in two nearby month.

Script

You can use this script in this way:
1. Open SQL Server Management Studio (SSMS) and connect to SQL Server.
2. Copy the code from SalesPercentage.sql, paste it in a new query and run the script.

After the script runs, we’ll get the following figure:

Here are some code snippet for your reference:

SQL

Edit|Remove

WITH SalePercentage_cte AS (
    SELECT
        ROW_NUMBER() OVER(PARTITION BY ProductName,SalesYear ORDER BY ProductName,SalesYear, SalesMonth) rn,
        ProductName,
        SalesYear,
        SalesMonth,
        Sales
    FROM  @Sales
)
SELECT s1.ProductName,s1.SalesYear,s1.SalesMonth,
    CAST(( ( s1.Sales - s2.Sales ) / s2.Sales ) * 100.0 AS DECIMAL(7, 2)) AS Percentage
FROM SalePercentage_cte AS s1
LEFT JOIN SalePercentage_cte s2
ON  s1.rn = s2.rn + 1 
    AND s1.ProductName = s2.ProductName 
    AND s1.SalesYear = s2.SalesYear

WITH SalePercentage_cte AS ( 
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY ProductName,SalesYear ORDER BY ProductName,SalesYear, SalesMonth) rn, 
        ProductName, 
        SalesYear, 
        SalesMonth, 
        Sales 
    FROM  @Sales 
) 
SELECT s1.ProductName,s1.SalesYear,s1.SalesMonth, 
    CAST(( ( s1.Sales - s2.Sales ) / s2.Sales ) * 100.0 AS DECIMAL(7, 2)) AS Percentage 
FROM SalePercentage_cte AS s1 
LEFT JOIN SalePercentage_cte s2 
ON  s1.rn = s2.rn + 1  
    AND s1.ProductName = s2.ProductName  
    AND s1.SalesYear = s2.SalesYear

Prerequisites

SQL Server 2005 or higher version