How to calculate Monthly and Quarterly Sales Growth in SQL Server

How to calculate Monthly and Quarterly Sales Growth using LAG Function in SQL Server
Scenario:
Suppose we have a Sample table with Sales by Year, Quarter and Month as follows:

/* Check and Drop the Table if already exists */

IF OBJECT_ID('dbo.tbl_Sales_Sample', 'U') IS NOT NULL

BEGIN

    DROP TABLE dbo.tbl_Sales_Sample;

END;

/* Create the sample Sales Table */

CREATE TABLE dbo.tbl_Sales_Sample (

Sales_Year INT,

Qtr_No VARCHAR(2),

        Month_No INT, 

        Total_Sales DECIMAL(10, 2)

);

/* Insert Sample Data */

INSERT INTO dbo.tbl_Sales_Sample (Sales_Year, Qtr_No, Month_No, Total_Sales) 

VALUES

(2021,'Q1', 1, 1000.00),

(2021,'Q1', 2, 1100.00),

(2021,'Q1', 3, 1200.00),

(2021,'Q2', 4, 1300.00),

(2021,'Q2', 5, 1250.00),

(2021,'Q2', 6, 1350.00),

(2021,'Q3', 7, 1400.00),

(2021,'Q3', 8, 1450.00),

(2021,'Q3', 9, 1500.00),

(2021,'Q4', 10, 1600.00),

(2021,'Q4', 11, 1550.00),

(2021,'Q4', 12, 1650.00),

(2022,'Q1', 1, 1700.00),

(2022,'Q1', 2, 1750.00),

(2022,'Q1', 3, 1800.00);

GO

SELECT * FROM dbo.tbl_Sales_Sample;

GO

Based on the above sample data, we will calculate the Monthly and Quarterly Sales Growth using the LAG () Function within CTEs as per below:

/* CTE for Monthly Sales Growth */

;WITH CTE_Monthly_Sales AS (

    SELECT 

        Sales_Year,

Qtr_No,

Month_No,

        SUM(Total_Sales) AS Total_Sales,

        LAG(SUM(Total_Sales)) OVER (ORDER BY Sales_Year, Month_No) AS Prev_Month_Sales

    FROM dbo.tbl_Sales_Sample

GROUP BY Sales_Year,Qtr_No,Month_No

)

SELECT 

    Sales_Year,

Qtr_No,

Month_No,

    Total_Sales,

    Prev_Month_Sales,

    CASE 

        WHEN Prev_Month_Sales IS NOT NULL 

THEN ((Total_Sales - Prev_Month_Sales) / Prev_Month_Sales * 100.0)

        ELSE NULL

    END AS Monthly_Sales_Growth

FROM CTE_Monthly_Sales;

GO

Result:

/* CTE for Quarterly Sales Growth */

;WITH CTE_Qtrly_Sales AS (

    SELECT 

        Sales_Year,

        Qtr_No,

        SUM(Total_Sales) AS Qtrly_Sales,

LAG(SUM(Total_Sales)) OVER (ORDER BY Sales_Year, Qtr_No) AS Prev_Qtr_Sales

    FROM dbo.tbl_Sales_Sample

    GROUP BY Sales_Year, Qtr_No

)

SELECT 

    Sales_Year,

    Qtr_No,

    Qtrly_Sales,

    Prev_Qtr_Sales,

    CASE 

        WHEN Prev_Qtr_Sales IS NOT NULL 

THEN ((Qtrly_Sales - Prev_Qtr_Sales) / Prev_Qtr_Sales * 100.0)

        ELSE NULL

    END AS Qtrly_Sales_Growth

FROM CTE_Qtrly_Sales;

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------