Return missing dates from a list in SQL Server 2012

Question

Considering the filter:

Start: 14/09/2015 (Monday)
End: 20/09/2015 (Sunday)

I have this result from a query from 14/09 until 20/09:

╔════════════╦══════════╦══════════════╦═══════════╗
║    Date    ║ Employee ║ EmployeeType ║   Type    ║
╠════════════╬══════════╬══════════════╬═══════════╣
║ 14/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 14/09/2015 ║ Silva    ║ Permanent    ║ Timesheet ║
║ 16/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 17/09/2015 ║ Airn     ║ Casual       ║ Timesheet ║
╚════════════╩══════════╩══════════════╩═══════════╝

I need to return all the missing dates if the employee is 'Permanent' and if the day missing is from 'Monday to Friday', as follows:

╔════════════╦══════════╦══════════════╦═══════════╗
║    Date    ║ Employee ║ EmployeeType ║   Type    ║
╠════════════╬══════════╬══════════════╬═══════════╣
║ 14/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 14/09/2015 ║ Silva    ║ Permanent    ║ Timesheet ║
║ 15/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 15/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 16/09/2015 ║ John     ║ Permanent    ║ Timesheet ║
║ 16/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 17/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 17/09/2015 ║ Airn     ║ Casual       ║ Timesheet ║
║ 17/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
║ 18/09/2015 ║ John     ║ Permanent    ║ Missing   ║
║ 18/09/2015 ║ Silva    ║ Permanent    ║ Missing   ║
╚════════════╩══════════╩══════════════╩═══════════╝

I don't understand very well the LEAD command in SQL Server 2012.

And what if the employee is not "Permanent"? If this criteria is relevant, please add a row or two to the sample data with non-"Permanent" EmployeeType and show us how the final result should look like. — Vladimir Baranov, Sep 16 '15 at 23:39
@FelixPamittan Yes, that's correct. Only 'Permanent Employees' will show the Missing Timesheets. And only from Mon-Fri — Roger Oliveira, Sep 16 '15 at 23:50
Is your sample data correct? Is John-Casual really the same John, the one with Permanent employeetype? — Felix Pamittan, Sep 16 '15 at 23:50
@FelixPamittan sorry, I fixed now. Also If there is another employee as permanent it should show records missing for him, I will update in the sample. — Roger Oliveira, Sep 16 '15 at 23:54
@RogerOliveira, yes, please, add another permanent employee to the data, just to clarify. Also, Would you expect to see a row for 17/09/2015 for John in the result? — Vladimir Baranov, Sep 16 '15 at 23:57
@VladimirBaranov yes, you guys are a legend, I added the missing one for John on the 17th.. — Roger Oliveira, Sep 17 '15 at 00:00

score 2 · Accepted Answer · edited May 23 '17 at 12:23

You can use a Tally Table to build the dates from @start and @end and CROSS JOIN it with your table to get the missing dates.

SQL Fiddle

DECLARE @start  DATE = '20150914',
        @end    DATE = '20150920'

;WITH E1(N) AS(
    SELECT 1 FROM(VALUES
        (1), (1), (1), (1), (1), (1), (1), (1), (1), (1)
    )t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b),
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b),
Tally(N) AS(
    SELECT TOP(DATEDIFF(DAY, @start, @end))
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM E4
),
CteDates(dt) AS(
    SELECT  DATEADD(DAY, N-1, @start) FROM Tally
),
CteEmployee AS(
    SELECT DISTINCT Employee, EmployeeType 
    FROM yourTable
    WHERE EmployeeType = 'Permanent'
)
SELECT
    Date = d.dt,
    e.Employee,
    e.EmployeeType,
    Type = ISNULL(yt.Type, 'Missing')
FROM CteEmployee e
CROSS JOIN CteDates d
LEFT JOIN yourTable yt
    ON e.Employee = yt.Employee
    AND d.dt = yt.Date
WHERE
    ((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)

UNION ALL

SELECT * FROM yourTable WHERE EmployeeType = 'Casual'
ORDER BY Date, e.Employee

The WHERE clause

((DATEPART(dw, d.dt) + @@DATEFIRST) % 7) NOT IN (0, 1)

excludes weekends regardless of @@DATEFIRST.

Explanation on Tally Table (E1, E2, E4, Tally)

Your sql works beautifully, however, it needs also to include that record 17/09/2015 ║ Airn ║ Casual ║ Timesheet in the result. — Roger Oliveira, Sep 17 '15 at 00:14
Also could you explain this part? WITH E1(N) AS( SELECT 1 FROM(VALUES (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) )t(N) — Roger Oliveira, Sep 17 '15 at 00:17
Perfect, I could successfully implement the query in my Stored Procedure with some adjusts... thanks — Roger Oliveira, Sep 17 '15 at 05:33

score 1 · Answer 2 · edited Nov 13 '18 at 23:14

I prefer to use permanent Calendar table for this type of queries, rather than generating a set of numbers on the fly. Calendar table is a special case of table of numbers.

For this query it is convenient to have a date and a flag IsWeekday in the Calendar table. I have few other fields in it in my database, see the links above for more details and ideas.

CREATE TABLE [dbo].[Calendar](
    [dt] [date] NOT NULL,
    [IsWeekday] [bit] NOT NULL,
CONSTRAINT [PK_Calendar] PRIMARY KEY CLUSTERED 
(
    [dt] ASC
))
GO

-- Init calendar table with dates from 2000-01-01 till 2136-11-22 (50K rows)
INSERT INTO dbo.Calendar (dt, IsWeekday)
SELECT TOP (50000)
    DATEADD(day, ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1, '2000-01-01') AS dt
    , 1 AS IsWeekday
FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
OPTION (MAXDOP 1);

-- Set IsWeekday flag
UPDATE dbo.Calendar
SET IsWeekday = 0
WHERE ((DATEPART(weekday, dt) + @@DATEFIRST) % 7) IN (0, 1);

Now we are ready. We'll find all permanent employees and generate a set of weekday dates for each permanent employee within the given range, then LEFT JOIN to it the original rows. This result will be unioned with the simple set of non-permanent employees.

Here is SQL Fiddle.

DECLARE @VarStartDate date = '2015-09-14';
DECLARE @VarEndDate date = '2015-09-20';
DECLARE @T TABLE (
    dt date, 
    Employee nvarchar(50), 
    EmployeeType varchar(50), 
    Tp varchar(50));

INSERT INTO @T (dt, Employee, EmployeeType, Tp) VALUES
('2015-09-14', 'John' , 'Permanent', 'Timesheet'),
('2015-09-14', 'Silva', 'Permanent', 'Timesheet'),
('2015-09-16', 'John' , 'Permanent', 'Timesheet'),
('2015-09-17', 'Airn' , 'Casual'   , 'Timesheet');

WITH
CTE_Employees
AS
(
    SELECT DISTINCT Employee, EmployeeType
    FROM @T
    WHERE EmployeeType = 'Permanent'
)
,CTE_Dates
AS
(
    SELECT
        CTE_Employees.Employee
        ,CTE_Employees.EmployeeType
        , dbo.Calendar.dt
    FROM
        CTE_Employees
        CROSS JOIN dbo.Calendar
    WHERE
        dbo.Calendar.dt >= @VarStartDate AND
        dbo.Calendar.dt <= @VarEndDate AND
        dbo.Calendar.IsWeekday = 1
)
SELECT
    CTE_Dates.dt
    ,CTE_Dates.Employee
    ,CTE_Dates.EmployeeType
    ,ISNULL(T.Tp, 'Missing') AS Tp
FROM
    CTE_Dates
    LEFT JOIN @T AS T ON
        T.Employee = CTE_Dates.Employee AND
        T.dt = CTE_Dates.dt

UNION ALL

SELECT
    T.dt
    ,T.Employee
    ,T.EmployeeType
    ,T.Tp
FROM @T AS T
WHERE EmployeeType <> 'Permanent'

ORDER BY dt, Employee;

Result

dt            Employee    EmployeeType    Tp
2015-09-14    John        Permanent       Timesheet
2015-09-14    Silva       Permanent       Timesheet
2015-09-15    John        Permanent       Missing
2015-09-15    Silva       Permanent       Missing
2015-09-16    John        Permanent       Timesheet
2015-09-16    Silva       Permanent       Missing
2015-09-17    Airn        Casual          Timesheet
2015-09-17    John        Permanent       Missing
2015-09-17    Silva       Permanent       Missing
2015-09-18    John        Permanent       Missing
2015-09-18    Silva       Permanent       Missing

Amazing, both solutions are very good. I wish I could Set both as the best answer. — Roger Oliveira, Sep 17 '15 at 01:08

Return missing dates from a list in SQL Server 2012

2 Answers2