Filling Missing Weekend Dates

Question

Post reply

Filling Missing Weekend Dates

SqlRookie

SSC Enthusiast

Points: 198
More actions
October 15, 2020 at 10:53 am

#3797555

Hi and first time poster, so please be gentle!
I have a table with the following sample data which excludes weekend dates for each employee:-
Employee Date Hours
John Doe 15-OCT-20 7.5
John Doe 16-OCT-20 7.5
John Doe 19-OCT-20 7.5
I would like to have the missing dates inserted and the hours to be 0:-
Employee Date Hours
John Doe 17-OCT-20 0
John Doe 18-OCT-20 0
I have been reading up on the use of CTE's, CALENDAR TABLES, CROSS JOINS etc, but I do not know the best approach to this.
Any pointers, or example code would be much appreciated
Many thanks in advance. 🙂
p.s. Not sure how to avoid gaps in between each sentence, sorry.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1380 More actions · Answer 1

You need two other "tables":

A Calendar table, as you've suggested, or the equivalent generated set of dates (e.g., using a Tally function and DATEADD). Search should provide plenty of examples of either approach.
Unique table of employees (needed to associate a name when plugging the gaps). This could be derived, e.g., using a CTE. My example pushes unique employees into a temp table.

e.g.,

DECLARE @StartDate DATE = '2020-10-15'
DECLARE @EndDate DATE = '2020-10-19'

DROP TABLE IF EXISTS #UniqueEmployees;
DROP TABLE IF EXISTS #Employee;

CREATE TABLE #Employee
    (
    [Employee] VARCHAR(50) NOT NULL,
    [Date] DATE NOT NULL,
    [Hours] decimal(5,2) NOT NULL,
    PRIMARY KEY ([Employee], [Date])
    );

CREATE TABLE #UniqueEmployees
    (Employee VARCHAR(50) NOT NULL PRIMARY KEY);

INSERT INTO #Employee
    ([Employee],[Date],[Hours])
VALUES  ('John Doe','2020-10-15',7.5),
        ('John Doe','2020-10-16',7.5),
        ('John Doe','2020-10-19',7.5);

INSERT INTO #UniqueEmployees (Employee)
SELECT DISTINCT Employee FROM #Employee;

SELECT #Employee.Employee, Calendar.CurrentDate, ISNULL(#Employee.Hours,0.0) AS Hours
FROM util.Calendar
CROSS JOIN #UniqueEmployees
LEFT JOIN #Employee ON #Employee.Employee = #UniqueEmployees.Employee AND #Employee.Date = Calendar.CurrentDate
WHERE Calendar.CurrentDate >= @StartDate
AND Calendar.CurrentDate <= @EndDate;