Get the Month with the Highest Sales for Each Product Query Using MS SQL Server?

To get the month with the highest sales for each product using Microsoft SQL Server, you need to:

1. Aggregate sales data by month and product to compute the total sales for each product per month.
2. Identify the month with the highest sales for each product.

Assuming you have the following tables:

• Sales table with columns: SaleDate, ProductID, SaleAmount.
• Products table with columns: ProductID, ProductName.

Here’s a step-by-step SQL query to achieve this:

SQL Query

-- Get the month with the highest sales for each product

WITH MonthlySales AS (

    SELECT

        p.ProductID,

        p.ProductName,

        DATEADD(MONTH, DATEDIFF(MONTH, 0, s.SaleDate), 0) AS SalesMonth, -- Start of the month

        SUM(s.SaleAmount) AS TotalSales

    FROM

        Sales s

        JOIN Products p ON s.ProductID = p.ProductID

    GROUP BY

        p.ProductID,

        p.ProductName,

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

),

RankedSales AS (

    SELECT

        ProductID,

        ProductName,

        SalesMonth,

        TotalSales,

        ROW_NUMBER() OVER (

            PARTITION BY ProductID

            ORDER BY TotalSales DESC

        ) AS rn

    FROM

        MonthlySales

)

SELECT

    ProductID,

    ProductName,

    SalesMonth,

    TotalSales

FROM

    RankedSales

WHERE

    rn = 1

ORDER BY

    ProductID;

Explanation

1. MonthlySales CTE:

   • DATEADD(MONTH, DATEDIFF(MONTH, 0, s.SaleDate), 0) AS SalesMonth: Computes the start of the month for each sale date.
   • SUM(s.SaleAmount) AS TotalSales: Aggregates the total sales amount for each product per month.
   • JOIN Products p ON s.ProductID = p.ProductID: Joins the Sales table with the Products table to get product details.
   • GROUP BY p.ProductID, p.ProductName, DATEADD(MONTH, DATEDIFF(MONTH, 0, s.SaleDate), 0): Groups by ProductID, ProductName, and SalesMonth to get the total sales per month for each product.

2. RankedSales CTE:

   • ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TotalSales DESC) AS rn: Assigns a rank to each month for each product based on total sales, with the highest sales getting rank 1.
   • PARTITION BY ProductID: Ensures the ranking is done separately for each product.

3. Main Query:

   • WHERE rn = 1: Filters to get only the month with the highest sales for each product.
   • ORDER BY ProductID: Orders the results by ProductID for better readability.

Additional Considerations

• Date Handling: Ensure that the SaleDate column in the Sales table is correctly populated with dates and that sales data is complete.
• Indexes: Indexes on SaleDate, ProductID, and SaleAmount can help improve query performance, especially with large datasets.
• Data Quality: Verify that the Sales table contains accurate and complete sales data.

Adjust column names and table structures based on your actual schema. This query helps you identify the month with the highest sales for each product.