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.

-- 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)

Now we join this table on the actual timesheet

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

2 comments
EP 12 Jan 2011 at 2:44 pm

Yes but during this adventure did it teach you to enter your timesheet on time LOL

Featured Articles

 Site Author

  • Thanks for visiting!