Oleg Oleg - 5 months ago 7
SQL Question

Why LEFT JOIN doesnt bring me all Month fields with NULL values

I have data only till April current month. So I am trying to use LEFT JOIN to bring the rest of the months till the end of the year by joining calendar table. .
Calendar table has YearNumber, MonthNumber and MonthName.
What am I doing wrong?

SELECT
Underwriter,
sum(case when TransactionType IN ('Policy', 'Reinstatement') then 1 ELSE 0 END) as PoliciesBound,
b.MonthNum,
b.YearNum,
b.MonthName

FROM tblCalendar b
LEFT JOIN Test_Plaza_ProductionReport a ON b.MonthNum=Month(a.EffectiveDate) AND b.YearNum = YEAR(a.EffectiveDate)
--FROM Test_Plaza_ProductionReport
WHERE Year(a.EffectiveDate)=2016 AND Underwriter <>'Batcheller, Jerry'
GROUP BY Underwriter,
b.YearNum,
b.MonthName,
b.MonthNum


Result should be like this:enter image description here

But my result only till April:
enter image description here

what am I missing by using LEFT JOIN?

Answer

As mentioned in my comment, the where criteria is negating your outer join. Try this instead:

...
FROM tblCalendar b  
    LEFT JOIN Test_Plaza_ProductionReport a ON     
        b.MonthNum=Month(a.EffectiveDate) AND 
        b.YearNum = YEAR(a.EffectiveDate) AND 
        a.Underwriter <>'Batcheller, Jerry'
WHERE       b.YearNum=2016 
GROUP BY    Underwriter,
            b.YearNum,
            b.MonthName,
            b.MonthNum

Note, I'm assuming the underwriter field is in the report table and not the calendar table.

Comments