理货表在两个日期之间插入缺失的日期?查询语句

2022-01-03 00:00:00 sql sql-server sql-server-2012

我从网上研究和我自己的 SQL 知识(不是最伟大的)拼凑了以下代码.

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

表 Table_One 保存员工及其工作日的数据，我想要做的是 INSERT 行之间的日期缺失(非工作日)每个工作人员的两个指定日期.

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.

这是我所能得到的，我不知道如何检查该行是否丢失，如果丢失，插入带有日期和相应员工数据的新 row.

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

桌子看起来像这样，

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.

例如.这个工作人员第一天是查询中SET @StartDate = '2016/6/1'后两天，

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.

这是上表中一名员工的结果，在本例中，员工编号为 1001，名为 Bill.

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

我有一个 While Loop 正在为我工作并更新丢失的记录，但性能很糟糕.

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

谢谢

推荐答案

另一个选项:(更新[再次])
创建并填充示例表(请在您的下一个问题中为我们保存此步骤)

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')

声明并填充@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, 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

验证插入:

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 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

相关文章