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?

Add missing date to query

2 Answers