Add missing date to request
I am working on a Query that returns dates based on the number of hours an employee has been working. If there are no hours on the day, no date is generated for the employee. 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
Return values:
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, I also want to return 2017-05-20 and 2017-05-21 even if they are not in the database.
How should I do it?
+3
source to share
2 answers
As @TimBiegeleisen's suggestion, you can use a calendar table 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
+1
source to share
You need to join the calender table. For example:
-- This will create a date range from 01-01-1900 to 01-01-2050
SELECT DATEADD(DAY, X.RN - 1, '19000102') Dates
INTO dbo.Calander
FROM (
SELECT TOP (DATEDIFF(DAY, '19000102', '20500101')) ROW_NUMBER() OVER(ORDER BY S1.object_id) RN
FROM sys.all_objects S1
CROSS JOIN sys.all_objects S2
) X
SELECT TOP 1000 SUM(AantalUur) as Uren
, C.Dates as Datum
FROM db gu
JOIN Medewerkers m
ON m.medewerker_pk = gu.medewerker_fk
RIGHT JOIN dbo.Calander C
ON C.Dates = m.GewerktOpDatum -- I assumed this is where GewerktOpDatum is. You should use aliases to make clear where attributes are coming from.
Where m.Voornaam = 'name'
and COALESCE(m.Tussenvoegsel,'') LIKE 'name'
and m.Achternaam = 'name'
and C.Dates between '2017-05-16 00:00:00.0' and '2017-05-23 00:00:00.0'
and UrenPerWeek > 0
GROUP BY C.Dates
0
source to share