I need to create a query or a stored procedure that will return all the time entry records for all employees - even if the employee didn't record time for the given day.
Time_Table
* EmployeeID
* ProjectID
* TimeEntryDate
* TimeEntryHours
I created a table called Date_Table that contains the date (DT_Date) for every day in the year.
I tried creating a view with the query like this:
SELECT EmployeeID, ProjectID, DT_Date, SUM(TimeEntryHours)
FROM Date_Table
LEFT OUTER JOIN Time_Table
ON DT_Date = TimeEntryDate
GROUP BY EmployeeID, ProjectID, DT_Date
I end up getting a bunch of null records back - so it isn't giving me the missing dates for each employee with a zero (0) for the hours. The project ID for the missing dates can be null.
I'm guessing I need to use a stored procedure rather than the query and loop thru each employee and create the record in a temp table for the missing dates. Would I still use the date table I created or is there a better way to loop thru the months and create the missing records for each day for each employee? Can anyone get me started on that? Thank You!
Start Free Trial
