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?