CTE with Left Join not returning NULL values

11 views Asked by At

I am using the following code to generate a CTE which is a single column of the last day of the month for the past 12 months:

WITH Dates 
AS
    (
    SELECT 
           EOMONTH(DATEADD(m,-1,(GETDATE()))) as MonthEnd -- Calculate end of previous month
    UNION ALL 
    SELECT EOMONTH(DATEADD(m, -1, MonthEnd))
      FROM dates
     WHERE MonthEnd >= EOMONTH(DATEADD(m,-11,(GETDATE()))) -- 12 months earlier 
    )

This works fine, it generates my list of the last 12 month end dates

I am linking this to another CTE which summarises sales by month grouped by the month end date using a LEFT JOIN as follows:

SELECT Dates.MonthEnd,
       Sales1.ProductGroupID,
       SUM(Sales1.SalesQty) AS TotalSales
  FROM Dates
       LEFT JOIN Sales1
         ON Dates.MonthEnd = Sales1.MonthEnd

I want the result to show NULL for ProductIDs where there have been no sales in a given month, but it is simply not returning a row for these months as if I were using an INNER JOIN. Is there something obvious that I am doing wrong or a limitation of the generated CTE? Is there a work-around?

0

There are 0 answers