CODEWITHSUNDEEP
sqlsql-servercounting
Zak Fischer
Zak Fischer

Reputation: 151

SQL - Tracking Monthly Sales

I am writing a query to summarize sales by month. My problem is that my query only returns records for months with sales. For example, I am looking over a 15 month range. But for one specific part in the example below only 3 of the 15 months had sales.

I'm hoping to have 15 records show up and the other ones have 0's for sales. The reason I am hoping for the additional records is I want to take the standard deviation of this, and dropping records impacts that calculation.

Sample Code:

SELECT I.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(*) as TotalDemand
FROM OrderDetails OD
INNER JOIN Orders O on O.Id = OD.OrderId
INNER JOIN Items I on I.Id = OD.ItemId
WHERE 
O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
AND I.PartNumber = '5144831-2'
GROUP BY I.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

Sample Current Output:

Part # | Year | Month | Demand
5144831-2   2017    1   1
5144831-2   2017    2   3
5144831-2   2016    3   1

Desired Output:

I would want an additional row such as:

5144831-2   2016    11  0

To show there were no sales in Nov 2016.

I do have a temp table #_date_array2 with the possible months/years, I think I need help incorporating a LEFT JOIN.

Upvotes: 1

Views: 265

Answers (3)

Vash
Vash

Reputation: 1787

If you want to use left join, you would not be able to use it directly with the inner join. You can do the inner join inside the parenthesis and then do the left join outside to avoid messing with the results of left join. Try this: 

SELECT Z.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(Z.OrderId) as TotalDemand
FROM Orders O 
LEFT JOIN 
(
    SELECT OrderId, PartNumber
    FROM
    OrderDetails OD 
    INNER JOIN Items I ON I.Id = OD.ItemId
    AND I.PartNumber = '5144831-2'
) Z
ON O.Id = Z.OrderId 
AND O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
GROUP BY Z.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

To get a count of 0 for months with no order, avoid using count(*) and use count(OrderId) as given above.

Note - You will have to make sure the Orders table has all months and years available i.e. if there is no CreateDate value of, say, November 2016 in the Orders table(left table in the join), the output will also not produce this month's entry.

Edit: Can you try this: 

SELECT Z.PartNumber as PartNumber,
YEAR(O.CreateDate) as CreateDateYear,
MONTH(O.CreateDate) as CreateDateMonth,
COUNT(O.OrderId) as TotalDemand
FROM Orders O 
RIGHT JOIN 
(
    SELECT OrderId, PartNumber
    FROM
    OrderDetails OD 
    INNER JOIN Items I ON I.Id = OD.ItemId
    AND I.PartNumber = '5144831-2'
) Z
ON O.Id = Z.OrderId 
AND O.CreateDate >= '1-1-2016' 
AND O.CreateDate <= '3-31-2017'
GROUP BY Z.PartNumber, YEAR(O.CreateDate) , MONTH(O.CreateDate);

Upvotes: 1

Matt
Matt

Reputation: 14341

based on all of your comments on other posts etc it seems like you have a table that has a date range you want and you want to be able to run the analysis for multiple/all of the part numbers. So the main issue is you will need a cartesian join between your date table and partnumbers that were sold during that time in order to accomplish you "0"s when not sold.

;WITH cteMaxMinDates AS (
    SELECT
       MinDate = MIN(DATEFROMPARTS(CreateDateYear,CreateDateMonth,1))
       ,MaxDate = MAX(DATEFROMPARTS(CreateDateYear,CreateDateMonth,1))
    FROM
       #_date_array2
)

;WITH cteOrderDetails AS (
    SELECT
       d.CreateDateYear
       ,d.CreateDateMonth
       ,I.PartNumber
    FROM
       #_date_array2 d
       INNER JOIN Orders o
       ON d.CreateDateMonth = MONTH(o.CreateDate)
       AND d.CreateDateYear = YEAR(o.CreateDate)
       INNER JOIN OrderDetails od
       ON o.Id = od.OrderId
       INNER JOIN Items i
       ON od.ItemId = i.Id
       AND i.PartNumber = '5144831-2'
)

, cteDistinctParts AS (
    SELECT DISTINCT PartNumber
    FROM
       cteOrderDetails
)

SELECT
    d.CreateDateYear
    ,d.CreateDateMonth
    ,I.PartNumber
    ,COUNT(od.PartNumber) as TotalDemand
FROM
    #_date_array2 d
    CROSS JOIN cteDistinctParts p
    LEFT JOIN cteOrderDetails od
    ON d.CreateDateYear = od.CreateDateYear
    AND d.CreateDateMonth = od.CreateDateMonth
    AND p.PartNumber = od.PartNumber
GROUP BY
    d.CreateDateYear
    ,d.CreateDateMonth
    ,I.PartNumber

To get ALL part numbers simply remove AND i.PartNumber = '5144831-2' join condition.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Assuming you have sales of something in every month, the simplest solution is to switch to conditional aggregation:

SELECT '5144831-2' as PartNumber,
       YEAR(O.CreateDate) as CreateDateYear,
       MONTH(O.CreateDate) as CreateDateMonth,
       SUM(CASE WHEN I.PartNumber = '5144831-2' THEN 1 ELSE 0 END) as TotalDemand
FROM OrderDetails OD INNER JOIN
     Orders O 
     ON O.Id = OD.OrderId INNER JOIN
     Items I 
     ON I.Id = OD.ItemId
WHERE O.CreateDate >= '2016-01-01' AND
      O.CreateDate <= '2017-03-31'
GROUP BY YEAR(O.CreateDate) , MONTH(O.CreateDate);

Note: This is something of a hack for solving the problem. More robust solutions involve generating the dates and using LEFT JOIN (or similar functionality). However, this is often the fastest way to get the result.

Upvotes: 1

Related Questions