Generating a Dynamic Date Table to Find Missing Dates

Generating a date table on the fly is nice to have for many reasons. The most recent of my adventures required determining when employees did not enter their time in their timesheets. I know I had a tattle tale job, the ironic part is that I ended up getting in trouble the next day for not inputting my hours. So, as for determining the dates when no one entered their time, the only way to determine this, was to cross join against a table of complete sequential dates. Yes it is an expensive query, however it’s the only way I think it can be done.

In this exercise, I needed to create a date table containing all dates for the last month. I needed to do this in order to cross join against all the days the employee had logged in order to retrieve a summary of hours. If an employee did not enter any hours for a particular day, then the day itself would not be represented on the report in order to show zero hours.

[cc lang=”sql”]
— Create our date table using a
— Common Table Expression (CTE)
DECLARE @EndDate datetime
DECLARE @StartDate datetime

— 30 days ago
SET @StartDate = GETDATE() – 30
— today
SET @EndDate = GETDATE()

;WITH Dates(DATEPARAM) AS
(
SELECT @StartDate AS datetime
UNION ALL
SELECT DATEADD(DAY, 1, DATEPARAM)
FROM Dates
WHERE DATEPARAM < @EndDate ) SELECT * FROM Dates OPTION (MAXRECURSION 10000) [/cc] Now we join this table on the actual timesheet [cc lang="sql"] SELECT emp.Employee, Day_Hours = SUM(ISNULL(CASE WHEN ts.DateEntered = dat.TheDate THEN ts.Day_Hours END),0), dat.TheDate FROM dbo.Employees emp JOIN TimeSheetItems ts ON ts.EmployeeID = emp.EmployeeID CROSS JOIN Dates dat GROUP BY emp.Employee, dat.TheDate [/cc]