Zak Fischer Zak Fischer - 1 year ago 94
SQL Question

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.

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download