Add missing date to query

Question

I've been working on a Query, which returns dates based on the amount of hours worked by an employee. If there are no hours worked on a date, the date is not created for an employee either. For example:

SELECT TOP 1000 SUM(AantalUur) as Uren, GewerktopDatum as Datum
  FROM db gu
 JOIN Medewerkers m on m.medewerker_pk = gu.medewerker_fk
  Where m.Voornaam = 'name' 
  and COALESCE(m.Tussenvoegsel,'') LIKE 'name' 
  and m.Achternaam = 'name' 
  and GewerktOpDatum between '2017-05-16 00:00:00.0' and  '2017-05-23 00:00:00.0'
  and UrenPerWeek > 0
        GROUP BY GewerktOpDatum

The returned values are:

8       2017-05-16 00:00:00.000
8       2017-05-17 00:00:00.000
8       2017-05-18 00:00:00.000
6       2017-05-19 00:00:00.000
8       2017-05-22 00:00:00.000
6,5     2017-05-23 00:00:00.000

So basically, I also want 2017-05-20, and 2017-05-21 to be returned, even though these are not in the database.

How would I go about doing this?

Answer 1

As @TimBiegeleisen's suggestion, you could use a calendar table by Recursive CTE.

DECLARE @StartDate date  = dateadd(month, -3, getdate()) -- or other day that you want....
DECLARE @EndDate date = getdate()

;WITH temp AS
(
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT dateadd(day,1,t.DateValue)
    FROM temp t
    WHERE t.DateValue <= @EndDate
)
SELECT t.DateValue, ISNULL(d.Uren ,0) AS Uren
FROM temp t
LEFT JOIN
(
  SELECT TOP 1000 SUM(AantalUur) as Uren, GewerktopDatum as Datum
  FROM db gu
    JOIN Medewerkers m on m.medewerker_pk = gu.medewerker_fk
  Where m.Voornaam = 'name' 
     and COALESCE(m.Tussenvoegsel,'') LIKE 'name' 
     and m.Achternaam = 'name' 
     and GewerktOpDatum between '2017-05-16 00:00:00.0' and  '2017-05-23 00:00:00.0'
     and UrenPerWeek > 0
  GROUP BY GewerktOpDatum

) d ON t.DateValue = d.Datum