另一个选项：（再次更新）
创建并填充示例表（ 请 在您的下一个问题中将这一步保存给我们）
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')
声明并填充
@StartDate和
@EndDate：
DECLARE @StartDate datetime = '2016-06-01', @EndDate datetime = '2016-08-01'
该INSERT...SELECt
语句：（我选择使用一个CTE，所以我就不用写了
dateadd(DD,ID-1,@StartDate)这么多次）
;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, SectionFROM @T TCROSS 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
验证插入物：
SELECt Staff_ID, [Date], [Year], Mon, [Day], First_Name, Last_Name, Section, Time_Worked FROM @T ORDER BY Staff_ID, [Date]
结果：
Staff_ID Date Year Mon Day First_Name Last_Name Section Time_Worked----------- ---------- ----------- ----------- ----------- ---------- ---------- ---------- -----------------------1001 2016-06-01 2016 61Bill Price Level 1 2016-06-01 08:30:00.0001001 2016-06-02 2016 62Bill Price Level 1 NULL1001 2016-06-03 2016 63Bill Price Level 1 NULL1001 2016-06-04 2016 64Bill Price Level 1 NULL1001 2016-06-05 2016 65Bill Price Level 1 2016-06-05 08:30:00.0001001 2016-06-06 2016 66Bill Price Level 1 NULL1001 2016-06-07 2016 67Bill Price Level 1 NULL1001 2016-06-08 2016 68Bill Price Level 1 NULL1001 2016-06-09 2016 69Bill Price Level 1 2016-06-09 08:30:00.0001001 2016-06-10 2016 610 Bill Price Level 1 NULL1001 2016-06-11 2016 611 Bill Price Level 1 NULL1001 2016-06-12 2016 612 Bill Price Level 1 NULL1001 2016-06-13 2016 613 Bill Price Level 1 NULL1001 2016-06-14 2016 614 Bill Price Level 1 NULL1001 2016-06-15 2016 615 Bill Price Level 1 NULL1001 2016-06-16 2016 616 Bill Price Level 1 NULL1001 2016-06-17 2016 617 Bill Price Level 1 NULL1001 2016-06-18 2016 618 Bill Price Level 1 NULL1001 2016-06-19 2016 619 Bill Price Level 1 NULL1001 2016-06-20 2016 620 Bill Price Level 1 NULL1001 2016-06-21 2016 621 Bill Price Level 1 NULL1001 2016-06-22 2016 622 Bill Price Level 1 NULL1001 2016-06-23 2016 623 Bill Price Level 1 NULL1001 2016-06-24 2016 624 Bill Price Level 1 NULL1001 2016-06-25 2016 625 Bill Price Level 1 NULL1001 2016-06-26 2016 626 Bill Price Level 1 NULL1001 2016-06-27 2016 627 Bill Price Level 1 NULL1001 2016-06-28 2016 628 Bill Price Level 1 NULL1001 2016-06-29 2016 629 Bill Price Level 1 NULL1001 2016-06-30 2016 630 Bill Price Level 1 NULL1001 2016-07-01 2016 71Bill Price Level 2 NULL1001 2016-07-02 2016 72Bill Price Level 2 NULL1001 2016-07-03 2016 73Bill Price Level 2 NULL1001 2016-07-04 2016 74Bill Price Level 2 NULL1001 2016-07-05 2016 75Bill Price Level 2 2016-06-12 08:30:00.0001001 2016-07-06 2016 76Bill Price Level 2 NULL1001 2016-07-07 2016 77Bill Price Level 2 NULL1001 2016-07-08 2016 78Bill Price Level 2 NULL1001 2016-07-09 2016 79Bill Price Level 2 NULL1001 2016-07-10 2016 710 Bill Price Level 2 NULL1001 2016-07-11 2016 711 Bill Price Level 2 NULL1001 2016-07-12 2016 712 Bill Price Level 2 NULL1001 2016-07-13 2016 713 Bill Price Level 2 NULL1001 2016-07-14 2016 714 Bill Price Level 2 NULL1001 2016-07-15 2016 715 Bill Price Level 2 NULL1001 2016-07-16 2016 716 Bill Price Level 2 NULL1001 2016-07-17 2016 717 Bill Price Level 2 NULL1001 2016-07-18 2016 718 Bill Price Level 2 NULL1001 2016-07-19 2016 719 Bill Price Level 2 NULL1001 2016-07-20 2016 720 Bill Price Level 2 NULL1001 2016-07-21 2016 721 Bill Price Level 2 NULL1001 2016-07-22 2016 722 Bill Price Level 2 NULL1001 2016-07-23 2016 723 Bill Price Level 2 NULL1001 2016-07-24 2016 724 Bill Price Level 2 NULL1001 2016-07-25 2016 725 Bill Price Level 2 NULL1001 2016-07-26 2016 726 Bill Price Level 2 NULL1001 2016-07-27 2016 727 Bill Price Level 2 NULL1001 2016-07-28 2016 728 Bill Price Level 2 NULL1001 2016-07-29 2016 729 Bill Price Level 2 NULL1001 2016-07-30 2016 730 Bill Price Level 2 NULL1001 2016-07-31 2016 731 Bill Price Level 2 NULL1002 2016-06-01 2016 61Mary Somers Level 1 2016-06-01 08:30:00.0001002 2016-06-02 2016 62Mary Somers Level 1 NULL1002 2016-06-03 2016 63Mary Somers Level 1 NULL1002 2016-06-04 2016 64Mary Somers Level 1 NULL1002 2016-06-05 2016 65Mary Somers Level 1 2016-06-05 08:30:00.0001002 2016-06-06 2016 66Mary Somers Level 1 NULL1002 2016-06-07 2016 67Mary Somers Level 1 NULL1002 2016-06-08 2016 68Mary Somers Level 1 2016-06-08 08:30:00.0001002 2016-06-09 2016 69Mary Somers Level 1 NULL1002 2016-06-10 2016 610 Mary Somers Level 1 NULL1002 2016-06-11 2016 611 Mary Somers Level 1 NULL1002 2016-06-12 2016 612 Mary Somers Level 1 NULL1002 2016-06-13 2016 613 Mary Somers Level 1 NULL1002 2016-06-14 2016 614 Mary Somers Level 1 NULL1002 2016-06-15 2016 615 Mary Somers Level 1 NULL1002 2016-06-16 2016 616 Mary Somers Level 1 NULL1002 2016-06-17 2016 617 Mary Somers Level 1 NULL1002 2016-06-18 2016 618 Mary Somers Level 1 NULL1002 2016-06-19 2016 619 Mary Somers Level 1 NULL1002 2016-06-20 2016 620 Mary Somers Level 1 NULL1002 2016-06-21 2016 621 Mary Somers Level 1 NULL1002 2016-06-22 2016 622 Mary Somers Level 1 NULL1002 2016-06-23 2016 623 Mary Somers Level 1 NULL1002 2016-06-24 2016 624 Mary Somers Level 1 NULL1002 2016-06-25 2016 625 Mary Somers Level 1 NULL1002 2016-06-26 2016 626 Mary Somers Level 1 NULL1002 2016-06-27 2016 627 Mary Somers Level 1 NULL1002 2016-06-28 2016 628 Mary Somers Level 1 NULL1002 2016-06-29 2016 629 Mary Somers Level 1 NULL1002 2016-06-30 2016 630 Mary Somers Level 1 NULL1003 2016-06-01 2016 61Mark Jones Level 1 NULL1003 2016-06-02 2016 62Mark Jones Level 1 NULL1003 2016-06-03 2016 63Mark Jones Level 1 2016-06-03 08:30:00.0001003 2016-06-04 2016 64Mark Jones Level 1 2016-06-05 08:30:00.0001003 2016-06-05 2016 65Mark Jones Level 1 NULL1003 2016-06-06 2016 66Mark Jones Level 1 NULL1003 2016-06-07 2016 67Mark Jones Level 1 NULL1003 2016-06-08 2016 68Mark Jones Level 1 NULL1003 2016-06-09 2016 69Mark Jones Level 1 NULL1003 2016-06-10 2016 610 Mark Jones Level 1 NULL1003 2016-06-11 2016 611 Mark Jones Level 1 NULL1003 2016-06-12 2016 612 Mark Jones Level 1 NULL1003 2016-06-13 2016 613 Mark Jones Level 1 NULL1003 2016-06-14 2016 614 Mark Jones Level 1 NULL1003 2016-06-15 2016 615 Mark Jones Level 1 NULL1003 2016-06-16 2016 616 Mark Jones Level 1 NULL1003 2016-06-17 2016 617 Mark Jones Level 1 NULL1003 2016-06-18 2016 618 Mark Jones Level 1 NULL1003 2016-06-19 2016 619 Mark Jones Level 1 NULL1003 2016-06-20 2016 620 Mark Jones Level 1 NULL1003 2016-06-21 2016 621 Mark Jones Level 1 NULL1003 2016-06-22 2016 622 Mark Jones Level 1 NULL1003 2016-06-23 2016 623 Mark Jones Level 1 NULL1003 2016-06-24 2016 624 Mark Jones Level 1 NULL1003 2016-06-25 2016 625 Mark Jones Level 1 NULL1003 2016-06-26 2016 626 Mark Jones Level 1 NULL1003 2016-06-27 2016 627 Mark Jones Level 1 NULL1003 2016-06-28 2016 628 Mark Jones Level 1 NULL1003 2016-06-29 2016 629 Mark Jones Level 1 NULL1003 2016-06-30 2016 630 Mark Jones Level 1 NULL
