sql server - Tally Table to insert missing dates between two dates? SQL

Question

I pieced together the below code from research from the net and my own SQL knowledge (not the greatest).

The table Table_One holds data for staff and their working days, what I am trying to do is INSERT rows where the dates are missing (non-working days) between two specified dates for each Staff member.

This is as far as I can get, I don't know how to check if the row is missing and if it is, insert the new row with the date and the corresponding staff members data.

SET NOCOUNT ON;
IF object_id('dbo.Tally') is not null drop table dbo.tally
GO
SELECT TOP 10000 IDENTITY(int,1,1) as ID
   INTO dbo.Tally FROM master.dbo.SysColumns
   ALTER table dbo.Tally
   add constraint PK_ID primary key clustered(ID)
GO
select * from dbo.Tally

--Generate Date Range
DECLARE @StartDate  datetime
DECLARE @EndDate datetime
SET @StartDate = '2016/6/1' 
SET @EndDate= '2016/7/1'
SELECT dateadd(DD,ID-1,@StartDate) as [DATE]
   FROM dbo.Tally
   WHERE dateadd(DD,ID-1,@StartDate)<=@EndDate

The table looks like this,

Staff_ID  |  Date      | Year   | Mon | Day |  First_Name   |  Last_Name  | Section  | Time_Worked
1001      |  2016/6/1  |  2016  |  6  |  1  |  Bill         |  Price      | Level 1  | 2016/6/1 8:30:00.000
1001      |  2016/6/5  |  2016  |  6  |  1  |   Bill        |  Price      | Level 1  | 2016/6/5 8:30:00.000
1001      |  2016/6/9  |  2016  |  6  |  1  |   Bill        |  Price      | Level 1  | 2016/6/9 8:30:00.000
1001      |  2016/6/12 |  2016  |  6  |  1  |   Bill        |  Price      | Level 1  | 2016/6/12 8:30:00.000
1002      |  2016/6/1  |  2016  |  6  |  1  |   Mary        |  Somers     | Level 1  | 2016/6/1 8:30:00.000
1002      |  2016/6/5  |  2016  |  6  |  1  |   Mary        |  Somers     | Level 1  | 2016/6/5 8:30:00.000
1002      |  2016/6/8  |  2016  |  6  |  1  |   Mary        |  Somers     | Level 1  | 2016/6/8 8:30:00.000
1003      |  2016/6/3  |  2016  |  6  |  1  |   Mark        |  Jones      | Level 1  | 2016/6/3 8:30:00.000
1003      |  2016/6/5  |  2016  |  6  |  1  |   Mark        |  Jones      | Level 1  | 2016/6/5 8:30:00.000

The first row of data that falls in between each of the two dates for the staff member will be able to be used to fill the columns other than the date column. And each staff member first row of data will not necessarily be the same date.

Eg. This staff members first day is two days after the SET @StartDate = '2016/6/1' in the query,

Staff_ID  |  Date      |  First_Name  |  Last_Name  | Section  | Time_Worked
1003      |  2016/6/3  |  Mark        |  Jones      | Level 1  | 2016/6/3 8:30:00.000

But , the other columns will be able to be used to fill the new rows data.

This is the outcome for one staff member from the table above, in this case ,staff No 1001 named Bill.

Staff_ID  |  Date       |  Year  |  Mon|  Day|  First_Name |  Last_Name  | Section  | Time_Worked
1001      |  2016/6/1   |  2016  |  6  |  1  | Bill        |  Price      | Level 1  | 2016/6/1 8:30:00.000
1001      |  2016/6/2   |  2016  |  6  |  2  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/3   |  2016  |  6  |  3  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/4   |  2016  |  6  |  4  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/5   |  2016  |  6  |  5  | Bill        |  Price      | Level 1  | 2016/6/5 8:30:00.000
1001      |  2016/6/6   |  2016  |  6  |  6  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/7   |  2016  |  6  |  7  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/8   |  2016  |  6  |  8  | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/9   |  2016  |  6  |  9  | Bill        |  Price      | Level 1  | 2016/6/9 8:30:00.000
1001      |  2016/6/10  |  2016  |  6  |  10 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/11  |  2016  |  6  |  11 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/12  |  2016  |  6  |  12 | Bill        |  Price      | Level 1  | 2016/6/12 8:30:00.000
1001      |  2016/6/13  |  2016  |  6  |  13 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/14  |  2016  |  6  |  14 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/15  |  2016  |  6  |  15 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/16  |  2016  |  6  |  16 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/17  |  2016  |  6  |  17 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/18  |  2016  |  6  |  18 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/19  |  2016  |  6  |  19 |  Bill       |  Price      | Level 1  | NULL
1001      |  2016/6/20  |  2016  |  6  |  20 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/21  |  2016  |  6  |  21 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/22  |  2016  |  6  |  22 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/23  |  2016  |  6  |  23 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/24  |  2016  |  6  |  24 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/25  |  2016  |  6  |  25 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/26  |  2016  |  6  |  26 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/27  |  2016  |  6  |  27 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/28  |  2016  |  6  |  28 | Bill        |  Price      | Level 1  | NULL
1001      |  2016/6/29  |  2016  |  6  |  29  | Bill       |  Price      | Level 1  | NULL
1001      |  2016/6/30  |  2016  |  6  |  30  | Bill       |  Price      | Level 1  | NULL

I have a While Loop that is working for me and updating the missing records for now, but the performance is terrible.

Thanks

Answer 1

Sample data

DECLARE @StartDate date = '2016-06-01';
DECLARE @EndDate   date = '2016-07-01';

DECLARE @Table_One TABLE (
    Staff_ID int, 
    dt date, 
    First_Name nvarchar(50), 
    Last_Name nvarchar(50), 
    Section  nvarchar(50), 
    Time_Worked datetime);

INSERT INTO @Table_One(Staff_ID, dt, First_Name, Last_Name, Section, Time_Worked) 
VALUES
(1001, '2016-06-01', 'Bill', 'Price ', 'Level 1', '2016-06-01 8:30:00.000'),
(1001, '2016-06-05', 'Bill', 'Price ', 'Level 1', '2016-06-05 8:30:00.000'),
(1001, '2016-06-09', 'Bill', 'Price ', 'Level 1', '2016-06-09 8:30:00.000'),
(1001, '2016-06-12', 'Bill', 'Price ', 'Level 1', '2016-06-12 8:30:00.000'),
(1002, '2016-06-01', 'Mary', 'Somers', 'Level 1', '2016-06-01 8:30:00.000'),
(1002, '2016-06-05', 'Mary', 'Somers', 'Level 1', '2016-06-05 8:30:00.000'),
(1002, '2016-06-08', 'Mary', 'Somers', 'Level 1', '2016-06-08 8:30:00.000'),
(1003, '2016-06-03', 'Mark', 'Jones ', 'Level 1', '2016-06-03 8:30:00.000'),
(1003, '2016-06-05', 'Mark', 'Jones ', 'Level 1', '2016-06-05 8:30:00.000');

Query

Query uses CROSS APPLY to "insert" rows when there is a gap in dates. It duplicates the current row as many times as needed using your Tally table of numbers.

There is a special handling of the case when the @StartDate is before the date of the first row. That's why there are two SELECTs unioned together.

The CTE.PrevDate IS NULL filters only such rows and they are repeated as many times as needed.

WITH
CTE
AS
(
    SELECT *
        ,LAG(dt) OVER (PARTITION BY Staff_ID ORDER BY dt) AS PrevDate
        ,LEAD(dt) OVER (PARTITION BY Staff_ID ORDER BY dt) AS NextDate
    FROM @Table_One AS T
)
SELECT
    Staff_ID
    ,NewDate
    ,First_Name
    ,Last_Name
    ,Section
    ,CASE WHEN NewDate = dt THEN Time_Worked ELSE NULL END AS Time_Worked
FROM
    CTE
    CROSS APPLY
    (
        SELECT DATEADD(day, Tally.ID - 1, CTE.dt) AS NewDate
        FROM dbo.Tally
        WHERE Tally.ID <= DATEDIFF(day, CTE.dt, ISNULL(CTE.NextDate, @EndDate))
    ) AS CA_Next

UNION ALL

SELECT
    Staff_ID
    ,NewDate
    ,First_Name
    ,Last_Name
    ,Section
    ,CASE WHEN NewDate = dt THEN Time_Worked ELSE NULL END AS Time_Worked
FROM
    CTE
    CROSS APPLY
    (
        SELECT DATEADD(day, - Tally.ID, CTE.dt) AS NewDate
        FROM dbo.Tally
        WHERE Tally.ID <= DATEDIFF(day, @StartDate, CTE.dt)
    ) AS CA_Prev
WHERE 
    CTE.PrevDate IS NULL

ORDER BY Staff_ID, NewDate;

Result

+----------+------------+------------+-----------+---------+-------------------------+
| Staff_ID |  NewDate   | First_Name | Last_Name | Section |       Time_Worked       |
+----------+------------+------------+-----------+---------+-------------------------+
|     1001 | 2016-06-01 | Bill       | Price     | Level 1 | 2016-06-01 08:30:00.000 |
|     1001 | 2016-06-02 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-03 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-04 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-05 | Bill       | Price     | Level 1 | 2016-06-05 08:30:00.000 |
|     1001 | 2016-06-06 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-07 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-08 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-09 | Bill       | Price     | Level 1 | 2016-06-09 08:30:00.000 |
|     1001 | 2016-06-10 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-11 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-12 | Bill       | Price     | Level 1 | 2016-06-12 08:30:00.000 |
|     1001 | 2016-06-13 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-14 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-15 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-16 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-17 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-18 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-19 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-20 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-21 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-22 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-23 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-24 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-25 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-26 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-27 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-28 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-29 | Bill       | Price     | Level 1 | NULL                    |
|     1001 | 2016-06-30 | Bill       | Price     | Level 1 | NULL                    |
|     1002 | 2016-06-01 | Mary       | Somers    | Level 1 | 2016-06-01 08:30:00.000 |
|     1002 | 2016-06-02 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-03 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-04 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-05 | Mary       | Somers    | Level 1 | 2016-06-05 08:30:00.000 |
|     1002 | 2016-06-06 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-07 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-08 | Mary       | Somers    | Level 1 | 2016-06-08 08:30:00.000 |
|     1002 | 2016-06-09 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-10 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-11 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-12 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-13 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-14 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-15 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-16 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-17 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-18 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-19 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-20 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-21 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-22 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-23 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-24 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-25 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-26 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-27 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-28 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-29 | Mary       | Somers    | Level 1 | NULL                    |
|     1002 | 2016-06-30 | Mary       | Somers    | Level 1 | NULL                    |
|     1003 | 2016-06-01 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-02 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-03 | Mark       | Jones     | Level 1 | 2016-06-03 08:30:00.000 |
|     1003 | 2016-06-04 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-05 | Mark       | Jones     | Level 1 | 2016-06-05 08:30:00.000 |
|     1003 | 2016-06-06 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-07 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-08 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-09 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-10 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-11 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-12 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-13 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-14 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-15 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-16 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-17 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-18 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-19 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-20 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-21 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-22 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-23 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-24 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-25 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-26 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-27 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-28 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-29 | Mark       | Jones     | Level 1 | NULL                    |
|     1003 | 2016-06-30 | Mark       | Jones     | Level 1 | NULL                    |
+----------+------------+------------+-----------+---------+-------------------------+

Inserting the generated rows back into the original table

At first I didn't realise that you want to change the original table, so I wrote a SELECT query that returns a needed result set. It is easy to adjust it to INSERT query that would add new rows into the original table.

All I did is added a filter WHERE NewDate <> dt, which ensures that only new rows that didn't exist before are inserted.

WITH
CTE
AS
(
    SELECT
        Staff_ID
        ,dt
        ,First_Name
        ,Last_Name
        ,Section
        ,Time_Worked
        ,LAG(dt) OVER (PARTITION BY Staff_ID ORDER BY dt) AS PrevDate
        ,LEAD(dt) OVER (PARTITION BY Staff_ID ORDER BY dt) AS NextDate
    FROM @Table_One AS T
)
INSERT INTO @Table_One(Staff_ID, dt, First_Name, Last_Name, Section, Time_Worked) 
SELECT
    Staff_ID
    ,NewDate
    ,First_Name
    ,Last