2

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

Improve this question
4
  • What do you mean by "a working loop"? Commented Jul 18, 2016 at 5:54
  • I have a query that uses a while loop that is currently updating the database with missing dates and data. But I have to loop through each staff member for each day in the month and with over 10,000 records it is taking too long. Commented Jul 18, 2016 at 5:59
  • 1
    You have shown how your data looks like. This is great. Please show us also how the final result should look like. Also, please add a tag with the version of SQL Server. Commented Jul 18, 2016 at 6:16
  • @VladimirBaranov, thanks, I have updated to show what the outcome needs to be like. Commented Jul 18, 2016 at 6:51

3 Answers 3

Reset to default
3

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_Name
    ,Section
    ,NULL 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
WHERE
    NewDate <> dt

UNION ALL

SELECT
    Staff_ID
    ,NewDate
    ,First_Name
    ,Last_Name
    ,Section
    ,NULL 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

To check the result just SELECT everything from the original table.

SELECT * FROM @Table_One ORDER BY Staff_ID, dt;

Result is the same as shown above.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Is it possible to have Time_Worked column as NULL for the new rows?
@KyloRen, yes, just add a CASE to compare the dates. I updated the answer.
Awesome, Up vote for the great help. Let me test everything ,but this looks like the answer. Once I have tested I will mark as the answer. Again , so many thanks for all the help!!!!
BTW, this is using my Tally Table?
I can't get this to work. The query goes through, but nothing is changed???
|
1

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
Improve this answer

22 Comments

I am not sure what you mean by 'Verify inserts' Do you place that code directly under the The INSERT...SELECT statement in the query window?
Never mind, I figured it out. Thanks so much for the help, I will make sure I make a sample data Table next time. Up voted for the great help
It's just a select statement to make sure the values where inserted correctly to the table. You don't actually need it, it's just a proof that the insert worked as expected.
"I will make sure I make a sample data Table next time." - you just made my day. seriously. you have no idea the amount of questions I comment with "please add the relevant table ddl and dml for sample data"...
I had no idea that this was needed, You made my day by informing me that it is needed. Have a great day!
|
1

Try following query

DECLARE @Id INT = 1
DECLARE @StartDate DATETIME = '2016.06.01'
DECLARE @EndDate DATETIME = '2016.06.30'

;WITH Dates (Id, [Date])
AS(

    SELECT @Id AS Id,  @StartDate AS [Date]

    UNION ALL

    SELECT Id + 1 AS Id , DATEADD(DAY, (Id), @StartDate) AS [Date]
    FROM Dates
    WHERE [Date] < @EndDate
)

SELECT
    Result.Staff_ID,
    Result.Date,
    Result.First_Name,
    Result.Last_Name,
    Result.Section,
    MT.Time_Worked
FROM
(       
    SELECT
        D.[Date],
        A.Staff_ID ,
        A.First_Name ,
        A.Last_Name ,
        A.Section
    FROM 
        Dates D CROSS JOIN 
        (
            SELECT DISTINCT 
                IMT.Staff_ID ,                
                IMT.First_Name ,
                IMT.Last_Name ,
                IMT.Section                 
            FROM 
                MainTable IMT
        ) A
) Result LEFT JOIN  
MainTable MT ON Result.[Date] = MT.dt AND Result.Staff_ID = MT.Staff_ID 
ORDER BY
    Result.Staff_ID,
    Result.[Date]
Improve this answer

2 Comments

If I am not mistaken, this will only work for one staff member? I need it to work for about 5000 + staff members
Yes, I thought you want this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.