KyloRen KyloRen - 4 months ago 43
SQL Question

Tally Table to insert missing dates between two dates? SQL

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 | First_Name | Last_Name | Section | Time_Worked
1001 | 2016/6/1 | Bill | Price | Level 1 | 2016/6/1 8:30:00.000
1001 | 2016/6/5 | Bill | Price | Level 1 | 2016/6/5 8:30:00.000
1001 | 2016/6/9 | Bill | Price | Level 1 | 2016/6/9 8:30:00.000
1001 | 2016/6/12 | Bill | Price | Level 1 | 2016/6/12 8:30:00.000
1002 | 2016/6/1 | Mary | Somers | Level 1 | 2016/6/1 8:30:00.000
1002 | 2016/6/5 | Mary | Somers | Level 1 | 2016/6/5 8:30:00.000
1002 | 2016/6/8 | Mary | Somers | Level 1 | 2016/6/8 8:30:00.000
1003 | 2016/6/3 | Mark | Jones | Level 1 | 2016/6/3 8:30:00.000
1003 | 2016/6/5 | 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

Another option: (updated [again])
Create and populate sample table (Please save us this step in your next question)

DECLARE @T as TABLE
(
    Staff_ID    int,
    [Date]      date, 
    [Year]      int,
    Mon         int,
    [Day]       int,
    First_Name  varchar(10),
    Last_Name   varchar(10),
    Section     varchar(10), 
    Time_Worked datetime
)

INSERT INTO @T VALUES
(1001, '2016-06-01', 2016, 6, 1, 'Bill', 'Price', 'Level 1', '2016-06-01 8:30:00.000'),
(1001, '2016-06-05', 2016, 6, 5, 'Bill', 'Price', 'Level 1', '2016-06-05 8:30:00.000'),
(1001, '2016-06-09', 2016, 6, 9, 'Bill', 'Price', 'Level 1', '2016-06-09 8:30:00.000'),
(1001, '2016-07-05', 2016, 7, 5, 'Bill', 'Price', 'Level 2', '2016-06-12 8:30:00.000'), -- Different month
(1002, '2016-06-01', 2016, 6, 1, 'Mary', 'Somers', 'Level 1', '2016-06-01 8:30:00.000'),
(1002, '2016-06-05', 2016, 6, 5, 'Mary', 'Somers', 'Level 1', '2016-06-05 8:30:00.000'),
(1002, '2016-06-08', 2016, 6, 8, 'Mary', 'Somers', 'Level 1', '2016-06-08 8:30:00.000'),
(1003, '2016-06-03', 2016, 6, 3, 'Mark', 'Jones', 'Level 1', '2016-06-03 8:30:00.000'),
(1003, '2016-06-04', 2016, 6, 4, 'Mark', 'Jones', 'Level 1', '2016-06-05 8:30:00.000')

Declare and populate @StartDate and @EndDate:

DECLARE @StartDate  datetime = '2016-06-01',
        @EndDate datetime = '2016-08-01'

The INSERT...SELECT statement: (I've chosen to use a cte so I wouldn't have to write the dateadd(DD,ID-1,@StartDate) so many times)

;WITH Calendar AS
(
    SELECT dateadd(DD,ID-1,@StartDate) as [Date]
    FROM dbo.Tally
    WHERE dateadd(DD,ID-1,@StartDate) < @EndDate
)

INSERT INTO @T (Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section)
SELECT DISTINCT Staff_ID, C.[Date], Year(C.[Date]), MONTH(C.[Date]), DAY(C.[Date]), First_Name, Last_Name, Section
FROM @T T
CROSS APPLY 
(
    SELECT Cal.[Date]
    FROM Calendar Cal
    WHERE MONTH(Cal.[Date]) = MONTH(T.[Date])
    AND YEAR(Cal.[Date]) = YEAR(T.[Date])
    AND NOT EXISTS
    (
        SELECT 1
        FROM @T T2
        WHERE T.Staff_ID = T2.Staff_ID 
        AND T2.[Date] = Cal.[Date]
    )
) C

Verify inserts:

SELECT Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section, Time_Worked 
FROM @T 
ORDER BY Staff_ID, [Date]

Results:

Staff_ID    Date       Year        Mon         Day         First_Name Last_Name  Section    Time_Worked
----------- ---------- ----------- ----------- ----------- ---------- ---------- ---------- -----------------------
1001        2016-06-01 2016        6           1           Bill       Price      Level 1    2016-06-01 08:30:00.000
1001        2016-06-02 2016        6           2           Bill       Price      Level 1    NULL
1001        2016-06-03 2016        6           3           Bill       Price      Level 1    NULL
1001        2016-06-04 2016        6           4           Bill       Price      Level 1    NULL
1001        2016-06-05 2016        6           5           Bill       Price      Level 1    2016-06-05 08:30:00.000
1001        2016-06-06 2016        6           6           Bill       Price      Level 1    NULL
1001        2016-06-07 2016        6           7           Bill       Price      Level 1    NULL
1001        2016-06-08 2016        6           8           Bill       Price      Level 1    NULL
1001        2016-06-09 2016        6           9           Bill       Price      Level 1    2016-06-09 08:30:00.000
1001        2016-06-10 2016        6           10          Bill       Price      Level 1    NULL
1001        2016-06-11 2016        6           11          Bill       Price      Level 1    NULL
1001        2016-06-12 2016        6           12          Bill       Price      Level 1    NULL
1001        2016-06-13 2016        6           13          Bill       Price      Level 1    NULL
1001        2016-06-14 2016        6           14          Bill       Price      Level 1    NULL
1001        2016-06-15 2016        6           15          Bill       Price      Level 1    NULL
1001        2016-06-16 2016        6           16          Bill       Price      Level 1    NULL
1001        2016-06-17 2016        6           17          Bill       Price      Level 1    NULL
1001        2016-06-18 2016        6           18          Bill       Price      Level 1    NULL
1001        2016-06-19 2016        6           19          Bill       Price      Level 1    NULL
1001        2016-06-20 2016        6           20          Bill       Price      Level 1    NULL
1001        2016-06-21 2016        6           21          Bill       Price      Level 1    NULL
1001        2016-06-22 2016        6           22          Bill       Price      Level 1    NULL
1001        2016-06-23 2016        6           23          Bill       Price      Level 1    NULL
1001        2016-06-24 2016        6           24          Bill       Price      Level 1    NULL
1001        2016-06-25 2016        6           25          Bill       Price      Level 1    NULL
1001        2016-06-26 2016        6           26          Bill       Price      Level 1    NULL
1001        2016-06-27 2016        6           27          Bill       Price      Level 1    NULL
1001        2016-06-28 2016        6           28          Bill       Price      Level 1    NULL
1001        2016-06-29 2016        6           29          Bill       Price      Level 1    NULL
1001        2016-06-30 2016        6           30          Bill       Price      Level 1    NULL
1001        2016-07-01 2016        7           1           Bill       Price      Level 2    NULL
1001        2016-07-02 2016        7           2           Bill       Price      Level 2    NULL
1001        2016-07-03 2016        7           3           Bill       Price      Level 2    NULL
1001        2016-07-04 2016        7           4           Bill       Price      Level 2    NULL
1001        2016-07-05 2016        7           5           Bill       Price      Level 2    2016-06-12 08:30:00.000
1001        2016-07-06 2016        7           6           Bill       Price      Level 2    NULL
1001        2016-07-07 2016        7           7           Bill       Price      Level 2    NULL
1001        2016-07-08 2016        7           8           Bill       Price      Level 2    NULL
1001        2016-07-09 2016        7           9           Bill       Price      Level 2    NULL
1001        2016-07-10 2016        7           10          Bill       Price      Level 2    NULL
1001        2016-07-11 2016        7           11          Bill       Price      Level 2    NULL
1001        2016-07-12 2016        7           12          Bill       Price      Level 2    NULL
1001        2016-07-13 2016        7           13          Bill       Price      Level 2    NULL
1001        2016-07-14 2016        7           14          Bill       Price      Level 2    NULL
1001        2016-07-15 2016        7           15          Bill       Price      Level 2    NULL
1001        2016-07-16 2016        7           16          Bill       Price      Level 2    NULL
1001        2016-07-17 2016        7           17          Bill       Price      Level 2    NULL
1001        2016-07-18 2016        7           18          Bill       Price      Level 2    NULL
1001        2016-07-19 2016        7           19          Bill       Price      Level 2    NULL
1001        2016-07-20 2016        7           20          Bill       Price      Level 2    NULL
1001        2016-07-21 2016        7           21          Bill       Price      Level 2    NULL
1001        2016-07-22 2016        7           22          Bill       Price      Level 2    NULL
1001        2016-07-23 2016        7           23          Bill       Price      Level 2    NULL
1001        2016-07-24 2016        7           24          Bill       Price      Level 2    NULL
1001        2016-07-25 2016        7           25          Bill       Price      Level 2    NULL
1001        2016-07-26 2016        7           26          Bill       Price      Level 2    NULL
1001        2016-07-27 2016        7           27          Bill       Price      Level 2    NULL
1001        2016-07-28 2016        7           28          Bill       Price      Level 2    NULL
1001        2016-07-29 2016        7           29          Bill       Price      Level 2    NULL
1001        2016-07-30 2016        7           30          Bill       Price      Level 2    NULL
1001        2016-07-31 2016        7           31          Bill       Price      Level 2    NULL
1002        2016-06-01 2016        6           1           Mary       Somers     Level 1    2016-06-01 08:30:00.000
1002        2016-06-02 2016        6           2           Mary       Somers     Level 1    NULL
1002        2016-06-03 2016        6           3           Mary       Somers     Level 1    NULL
1002        2016-06-04 2016        6           4           Mary       Somers     Level 1    NULL
1002        2016-06-05 2016        6           5           Mary       Somers     Level 1    2016-06-05 08:30:00.000
1002        2016-06-06 2016        6           6           Mary       Somers     Level 1    NULL
1002        2016-06-07 2016        6           7           Mary       Somers     Level 1    NULL
1002        2016-06-08 2016        6           8           Mary       Somers     Level 1    2016-06-08 08:30:00.000
1002        2016-06-09 2016        6           9           Mary       Somers     Level 1    NULL
1002        2016-06-10 2016        6           10          Mary       Somers     Level 1    NULL
1002        2016-06-11 2016        6           11          Mary       Somers     Level 1    NULL
1002        2016-06-12 2016        6           12          Mary       Somers     Level 1    NULL
1002        2016-06-13 2016        6           13          Mary       Somers     Level 1    NULL
1002        2016-06-14 2016        6           14          Mary       Somers     Level 1    NULL
1002        2016-06-15 2016        6           15          Mary       Somers     Level 1    NULL
1002        2016-06-16 2016        6           16          Mary       Somers     Level 1    NULL
1002        2016-06-17 2016        6           17          Mary       Somers     Level 1    NULL
1002        2016-06-18 2016        6           18          Mary       Somers     Level 1    NULL
1002        2016-06-19 2016        6           19          Mary       Somers     Level 1    NULL
1002        2016-06-20 2016        6           20          Mary       Somers     Level 1    NULL
1002        2016-06-21 2016        6           21          Mary       Somers     Level 1    NULL
1002        2016-06-22 2016        6           22          Mary       Somers     Level 1    NULL
1002        2016-06-23 2016        6           23          Mary       Somers     Level 1    NULL
1002        2016-06-24 2016        6           24          Mary       Somers     Level 1    NULL
1002        2016-06-25 2016        6           25          Mary       Somers     Level 1    NULL
1002        2016-06-26 2016        6           26          Mary       Somers     Level 1    NULL
1002        2016-06-27 2016        6           27          Mary       Somers     Level 1    NULL
1002        2016-06-28 2016        6           28          Mary       Somers     Level 1    NULL
1002        2016-06-29 2016        6           29          Mary       Somers     Level 1    NULL
1002        2016-06-30 2016        6           30          Mary       Somers     Level 1    NULL
1003        2016-06-01 2016        6           1           Mark       Jones      Level 1    NULL
1003        2016-06-02 2016        6           2           Mark       Jones      Level 1    NULL
1003        2016-06-03 2016        6           3           Mark       Jones      Level 1    2016-06-03 08:30:00.000
1003        2016-06-04 2016        6           4           Mark       Jones      Level 1    2016-06-05 08:30:00.000
1003        2016-06-05 2016        6           5           Mark       Jones      Level 1    NULL
1003        2016-06-06 2016        6           6           Mark       Jones      Level 1    NULL
1003        2016-06-07 2016        6           7           Mark       Jones      Level 1    NULL
1003        2016-06-08 2016        6           8           Mark       Jones      Level 1    NULL
1003        2016-06-09 2016        6           9           Mark       Jones      Level 1    NULL
1003        2016-06-10 2016        6           10          Mark       Jones      Level 1    NULL
1003        2016-06-11 2016        6           11          Mark       Jones      Level 1    NULL
1003        2016-06-12 2016        6           12          Mark       Jones      Level 1    NULL
1003        2016-06-13 2016        6           13          Mark       Jones      Level 1    NULL
1003        2016-06-14 2016        6           14          Mark       Jones      Level 1    NULL
1003        2016-06-15 2016        6           15          Mark       Jones      Level 1    NULL
1003        2016-06-16 2016        6           16          Mark       Jones      Level 1    NULL
1003        2016-06-17 2016        6           17          Mark       Jones      Level 1    NULL
1003        2016-06-18 2016        6           18          Mark       Jones      Level 1    NULL
1003        2016-06-19 2016        6           19          Mark       Jones      Level 1    NULL
1003        2016-06-20 2016        6           20          Mark       Jones      Level 1    NULL
1003        2016-06-21 2016        6           21          Mark       Jones      Level 1    NULL
1003        2016-06-22 2016        6           22          Mark       Jones      Level 1    NULL
1003        2016-06-23 2016        6           23          Mark       Jones      Level 1    NULL
1003        2016-06-24 2016        6           24          Mark       Jones      Level 1    NULL
1003        2016-06-25 2016        6           25          Mark       Jones      Level 1    NULL
1003        2016-06-26 2016        6           26          Mark       Jones      Level 1    NULL
1003        2016-06-27 2016        6           27          Mark       Jones      Level 1    NULL
1003        2016-06-28 2016        6           28          Mark       Jones      Level 1    NULL
1003        2016-06-29 2016        6           29          Mark       Jones      Level 1    NULL
1003        2016-06-30 2016        6           30          Mark       Jones      Level 1    NULL
Comments