Get the Number of Products Sold Each Month for the Last Year Query Using MS SQL Server?

To get the number of products sold each month for the last year using Microsoft SQL Server, you need to:

1. Filter the sales data for the last year.
2. Aggregate the number of products sold for each month.

Assuming you have the following tables:

• OrderItems table with columns: OrderID, ProductID, Quantity.
• Orders table with columns: OrderID, OrderDate.

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

SQL Query

-- Get the number of products sold each month for the last year

WITH MonthlySales AS (

    SELECT

        DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) AS SalesMonth, -- Start of the month

        SUM(oi.Quantity) AS TotalProductsSold

    FROM

        Orders o

        JOIN OrderItems oi ON o.OrderID = oi.OrderID

    WHERE

        o.OrderDate >= DATEADD(YEAR, -1, GETDATE()) -- Filter for the last year

    GROUP BY

        DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0)

)

SELECT

    SalesMonth,

    TotalProductsSold

FROM

    MonthlySales

ORDER BY

    SalesMonth;

Explanation

1. MonthlySales CTE:

   • DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0) AS SalesMonth: Calculates the start of the month for each order date to aggregate sales by month.
   • SUM(oi.Quantity) AS TotalProductsSold: Sums the quantities of products sold each month.
   • WHERE o.OrderDate >= DATEADD(YEAR, -1, GETDATE()): Filters the data to include only orders from the last year.
   • GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, o.OrderDate), 0): Groups the results by month to compute the total number of products sold each month.

2. Main Query:

   • SELECT SalesMonth, TotalProductsSold: Selects the month and the total number of products sold.
   • ORDER BY SalesMonth: Orders the results by the start of the month for chronological presentation.

Additional Considerations

• Date Handling: Ensure that the OrderDate column in the Orders table is properly populated with dates. Handle cases where orders may not have a valid OrderDate.
• Performance: Indexes on OrderDate and Quantity can improve query performance, especially with large datasets.
• Data Quality: Verify that the OrderItems table correctly reflects the quantities sold and that there are no discrepancies in the data.

Adjust column names and table structures based on your actual schema. This query will effectively provide the number of products sold each month for the last year.