Calculate the Moving Average of Sales Over the Last 3 Months

Calculate the Moving Average of Sales Over the Last 3 Months Query Using MS SQL Server?

To calculate the moving average of sales over the last 3 months in Microsoft SQL Server, you can use window functions to compute the average of sales over a specified period. This involves:

1. Aggregating sales data by month.
2. Using the ROWS BETWEEN clause to calculate the moving average over a 3-month window.

Assuming you have a Sales table with the following columns:

• SaleDate: The date of the sale.
• SaleAmount: The amount of the sale.

Here’s a step-by-step SQL query to calculate the moving average of sales over the last 3 months:

SQL Query

-- Calculate the moving average of sales over the last 3 months

WITH MonthlySales AS (

    SELECT

        DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS MonthStart, -- Start of the month

        SUM(SaleAmount) AS MonthlyTotal

    FROM

        Sales

    GROUP BY

        DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0)

),

MovingAverage AS (

    SELECT

        MonthStart,

        MonthlyTotal,

        AVG(MonthlyTotal) OVER (

            ORDER BY MonthStart

            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

        ) AS MovingAverage

    FROM

        MonthlySales

)

SELECT

    MonthStart,

    MonthlyTotal,

    MovingAverage

FROM

    MovingAverage

ORDER BY

    MonthStart;

Explanation

1. MonthlySales CTE:

   • DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0) AS MonthStart: Computes the start of the month for each sale date.
   • SUM(SaleAmount) AS MonthlyTotal: Aggregates the total sales amount for each month.
   • GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, SaleDate), 0): Groups sales data by month to get monthly totals.

2. MovingAverage CTE:

   • AVG(MonthlyTotal) OVER (ORDER BY MonthStart ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage: Computes the moving average over the current month and the previous 2 months (a 3-month window). The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW clause specifies the window of 3 months.

3. Main Query:

   • SELECT MonthStart, MonthlyTotal, MovingAverage: Selects the month, the total sales for that month, and the moving average.
   • ORDER BY MonthStart: Orders the results by the start of the month for chronological presentation.

Additional Considerations

• Date Range: Ensure your Sales table covers a sufficiently long period to get meaningful results from the moving average calculation.
• Handling Null Values: If there are months with no sales, they will not appear in the MonthlySales CTE. Adjust the query if you need to handle such cases.
• Performance: Indexes on SaleDate and any relevant columns can improve query performance.

Adjust column names and table structure based on your actual schema. This query provides a method to calculate the moving average of sales over a 3-month window, which helps in understanding sales trends.