Can't get missing days to show zero

Question

Here is what I've written - I'm trying to get the dates without transactions to just show zero for the field. I've gone to google and talked to everyone I know, this should work - but isn't :?

IF OBJECT_ID('tempdb..#D') IS NOT NULL
    DROP TABLE #D;

CREATE TABLE #D
(
    [Date] [DATE] NOT NULL,
    PRIMARY KEY CLUSTERED ([Date] ASC)
);

DECLARE @CurrentDate DATE = GETDATE() - 95;
DECLARE @EndDate DATE = GETDATE();

WHILE @CurrentDate < @EndDate
BEGIN
    INSERT INTO #D
    (
        [Date]
    )
    SELECT DATE = @CurrentDate;
    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;

--SELECT * FROM #D ORDER BY  Date DESC


SELECT ITEM2_NUM AS 'Part Number',
       COALESCE(SUM(TRANS_QTY), 0) 'Daily Usage',
       D.Date AS 'Usage Date'
FROM #D AS D
    LEFT OUTER JOIN dbo.W_INVENTORY_TRANS_F IT
        ON IT.GL_DT = D.Date
    LEFT JOIN dbo.W_BU_ITEM_D BI
        ON BI.BUSINESS_UNIT_WID = IT.BUSINESS_UNIT_WID
           AND BI.ITEM_WID = IT.ITEM_WID
WHERE DOCUMENT_TYPE_WID = 22
      AND D.Date >= DATEADD(yy, -1, GETDATE())
      AND IT.BUSINESS_UNIT_WID = '837'
      AND IT.ITEM2_NUM = '10111'
      AND BI.STOCKING_TYPE = 'P'
GROUP BY ITEM2_NUM,
         D.Date
ORDER BY D.Date DESC;

Below is the results of what I get:

Part Number Daily Usage Usage Date
10111   -331.0000   2019-08-19
10111   -2617.0000  2019-08-16
10111   -418.0000   2019-08-15
10111   -471.0000   2019-08-14
10111   -1158.0000  2019-08-13
10111   -766.0000   2019-08-12
10111   -1385.0000  2019-08-09

This is what I want:

Part Number Daily Usage Usage Date
10111   -331    8/19/2019
10111     0     8/18/2019
10111     0     8/17/2019
10111   -2617   8/16/2019
10111   -418    8/15/2019
10111   -471    8/14/2019
10111   -1158   8/13/2019
10111   -766    8/12/2019
10111     0     8/11/2019
10111     0     8/10/2019
10111   -1385   8/9/2019

Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS If you don't make the effort to clearly say what you want then you can't understand, reason, communicate or search. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. — philipxy, Aug 29 '19 at 20:03
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, Aug 29 '19 at 20:06
This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & with & without 'site:stackoverflow.com' and read many hits & answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Aug 29 '19 at 20:08
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Aug 29 '19 at 20:09

score 2 · Answer 1 · answered Aug 20 '19 at 18:53

Your where clause is effectively changing your left join to an inner join. That is because a left join returns nulls for rows that can't be found on the right table, and null compared to anything will always return unknown which is interpreted by the SQL Server as false.

What you need to do is move all the conditions that's referencing the right side tables into the on clause.
Also, you are grouping by ITEM2_NUM which doesn't make sense because it's already filtered out in the where clause.

Here's what should be a working version of your code (except the DOCUMENT_TYPE_WID column which I don't know what table it belongs to):

SELECT '10111' AS 'Part Number',
       COALESCE(SUM(TRANS_QTY), 0) 'Daily Usage',
       D.Date AS 'Usage Date'
FROM #D AS D
    LEFT OUTER JOIN dbo.W_INVENTORY_TRANS_F IT
        ON IT.GL_DT = D.Date
        AND IT.BUSINESS_UNIT_WID = '837'
        AND IT.ITEM2_NUM = '10111'
        -- I had to guess where this column belongs, it might be on the BI table
        AND IT.DOCUMENT_TYPE_WID = 22
    LEFT JOIN dbo.W_BU_ITEM_D BI
        ON BI.BUSINESS_UNIT_WID = IT.BUSINESS_UNIT_WID
        AND BI.ITEM_WID = IT.ITEM_WID
        AND BI.STOCKING_TYPE = 'P'
WHERE D.Date >= DATEADD(yy, -1, GETDATE())
GROUP BY D.Date
ORDER BY D.Date DESC;

score 0 · Answer 2 · answered Aug 29 '19 at 17:38

Zohar was 100% correct - for those that end up here at some point below if the final code I ended up using.

IF OBJECT_ID('tempdb..#D') IS NOT NULL
    DROP TABLE #D;

CREATE TABLE #D
(
    [Date] [DATE] NOT NULL,
    PRIMARY KEY CLUSTERED ([Date] ASC)
);

DECLARE @CurrentDate DATE = GETDATE() - 95;
DECLARE @EndDate DATE = GETDATE();

WHILE @CurrentDate < @EndDate
BEGIN
    INSERT INTO #D
    (
        [Date]
    )
    SELECT DATE = @CurrentDate;
    SET @CurrentDate = DATEADD(DD, 1, @CurrentDate);
END;

IF OBJECT_ID('tempdb..#DailyUsage') IS NOT NULL
    DROP TABLE #DailyUsage;

CREATE TABLE #DailyUsage
(
    BU_ITEM_WID INT,
    TRANS_QTY DECIMAL(15, 4),
    GL_DT DATETIME
);
INSERT INTO #DailyUsage
(
    BU_ITEM_WID,
    TRANS_QTY,
    GL_DT
)
SELECT BU_ITEM_WID,
       TRANS_QTY AS 'Daily Usage',
       GL_DT
FROM dbo.W_INVENTORY_TRANS_F
WHERE 1 = 1
      AND DOCUMENT_TYPE_WID = 22
      AND GL_DT >= GETDATE() - 95;

CREATE NONCLUSTERED INDEX [IX_DailyUsage]
ON #DailyUsage (BU_ITEM_WID)
INCLUDE
(
    TRANS_QTY,
    GL_DT
);

WITH cte_ItemNums
AS (SELECT DISTINCT
        IT.ITEM2_NUM,
        IT.BU_ITEM_WID
    FROM W_INVENTORY_TRANS_F IT
        LEFT JOIN dbo.W_BU_ITEM_D BI
            ON BI.BUSINESS_UNIT_WID = IT.BUSINESS_UNIT_WID
               AND BI.ITEM_WID = IT.ITEM_WID
               AND BI.STOCKING_TYPE = 'P'
    WHERE 1 = 1
          AND IT.DOCUMENT_TYPE_WID = 22
          AND IT.BUSINESS_UNIT_WID = '837'
   )
SELECT IT1.ITEM2_NUM,
       COALESCE(SUM(IT2.TRANS_QTY), 0) AS 'Daily Usage',
       D.Date AS 'Usage Date'
FROM #D AS D
    LEFT OUTER JOIN cte_ItemNums IT1
        ON 1 = 1
    LEFT OUTER JOIN #DailyUsage IT2
        ON IT2.GL_DT = D.Date
           AND IT1.BU_ITEM_WID = IT2.BU_ITEM_WID
GROUP BY IT1.ITEM2_NUM,
         D.Date;

Can't get missing days to show zero

2 Answers2