理货表在两个日期之间插入缺失的日期?查询语句 [英] Tally Table to insert missing dates between two dates? SQL
本文介绍了理货表在两个日期之间插入缺失的日期?查询语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我从网上研究和我自己的 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
这篇关于理货表在两个日期之间插入缺失的日期?查询语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
