Right Join to CTE_dates not returning all dates

Question

Long time lurker but first time posting; I can't for the life of me work this out on my own, or find an answer anywhere that solves this.

I have table1 with columns department, sales, date. Each row records a sale.

I want a query to return the last 7 days (weekends incl) of sales by department (using Sum()), with days where no sales are made (no records for the date) still returned with value of 0).

At the moment I am using a CTE to generate a list of dates for the last 7 days, code is as follows:

DECLARE @startDate datetime, @endDate datetime; 

SET @startDate = DateAdd(day, -6, GetDate()); 
SET @endDate   = GetDate(); 

WITH [dates_CTE] ([date]) AS 
   (SELECT @startDate AS [date] 
    UNION ALL 
    SELECT DATEADD(d, 1, [date]) AS [date] 
    FROM [dates_CTE]
    WHERE [date] < @endDate) 

--This part just to show contents of CTE, not used in full code
SELECT [date], CONVERT(date, [date])
FROM [dates_CTE]
ORDER BY [date] DESC

Modified from source: https://gallery.technet.microsoft.com/scriptcenter/97fe6de5-ab27-40db-8565-637988f028a2

Now I am trying to use this code to select data from Table1, and include a row for a missing date, where the sales value should be NULL (once I get this working, I will just use ISNULL to change to 0):

SELECT SUM([Table1].[sales]) AS 'Total Sales', CONVERT(date, [Table1].[date]) AS 'Date'
FROM [Table1]
RIGHT JOIN [dates_CTE]
ON CONVERT(date, [Table1].[date]) = CONVERT(date, [dates_CTE].[date])
WHERE [Department] = 'ELC'
GROUP BY CONVERT(date, [Table1].[date])
ORDER BY CONVERT(date, [Table1].[date]) DESC

You may notice I've gone a little over the top with the CONVERT(date,) function. This is because Table1.date only records the date, and puts in all zeroes for the time; i.e. YYYY-MM-DD 00:00:00.000. My CTE get's the current datetime, and obviously that will have something other than zeroes in the time; so I am using CONVERT to turn them both only into dates and ignore the times, as they are irrelevant.

No matter what I seem to do (I've played around with different joins, and not using the CONVERT(date,), to no avail. The output seems to always be the same; it only returns 5 records with values, when I need 2 more which should just read null/0.

Answer 1

Which table does column [Department] belong to?

If it's a [Table1] column, move the [Department] = 'ELC' condition from WHERE to ON, i.e.

SELECT SUM([Table1].[sales]) AS 'Total Sales',
       CONVERT(date, [Table1].[date]) AS 'Date'
FROM [Table1]
RIGHT JOIN [dates_CTE]
ON CONVERT(date, [Table1].[date]) = CONVERT(date, [dates_CTE].[date])
   AND [Department] = 'ELC'
GROUP BY CONVERT(date, [Table1].[date])
ORDER BY CONVERT(date, [Table1].[date]) DESC

If the condition is in WHERE, the outer join executes as a regular inner join. Move to ON to get true outer join behavior.