sql - 添加缺失日期以查询

Question

我一直在研究一个查询，它根据员工的工作小时数返回日期。如果某个日期没有工作小时数，则也不会为员工创建该日期。例如:

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

返回值是:

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

所以基本上，我还希望返回 2017-05-20 和 2017-05-21，即使它们不在数据库中。

我该怎么做？

Answer 1

根据@TimBiegeleisen 的建议，您可以通过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