如何在SQL Server中查找日期中的空白？(How to find gaps in dates in SQL Server?)

如何在SQL Server中查找日期中的空白？(How to find gaps in dates in SQL Server?)

我有一张桌子

DATE LEAVEDAYS NXTWRKDAYS 2014-07-01 No NULL 2014-07-02 No NULL 2014-07-03 No NULL 2014-07-04 No NULL 2014-07-05 Yes NULL 2014-07-06 Yes NULL 2014-07-07 Yes NULL 2014-07-08 No NULL 2014-07-09 Yes NULL 2014-07-10 Yes NULL 2014-07-11 No NULL

输出将是

DATE LEAVEDAYS NXTWRKDAYS 2014-07-01 No 2014-07-02 2014-07-02 No 2014-07-03 2014-07-03 No 2014-07-04 2014-07-04 No 2014-07-05 2014-07-05 Yes 2014-07-08 2014-07-06 Yes 2014-07-08 2014-07-07 Yes 2014-07-08 2014-07-08 No 2014-07-09 2014-07-09 Yes 2014-07-11 2014-07-10 Yes 2014-07-11 2014-07-11 No 2014-07-12

可以看出，在2014-07-04之后，下一个工作日是'2014-07-08'，从此以后2014-07-05之后的产量是2014-07-08。 2014-07-10之后，下一个工作日为'2014-07-11'，此后2014-07-10之后的产量为2014-07-11

脚本

declare @t table([DATE] DATE,LEAVEDAYS Varchar(10),NXTWRKDAYS DATE) insert into @t Values ('2014-07-01','No',NULL), ('2014-07-02','No',NULL), ('2014-07-03','No',NULL), ('2014-07-04','No',NULL), ('2014-07-05','Yes',NULL), ('2014-07-06','Yes',NULL), ('2014-07-07','Yes',NULL), ('2014-07-08','No',NULL), ('2014-07-09','Yes',NULL), ('2014-07-10','Yes',NULL), ('2014-07-11','No',NULL) select * from @t

I have a table as

DATE LEAVEDAYS NXTWRKDAYS 2014-07-01 No NULL 2014-07-02 No NULL 2014-07-03 No NULL 2014-07-04 No NULL 2014-07-05 Yes NULL 2014-07-06 Yes NULL 2014-07-07 Yes NULL 2014-07-08 No NULL 2014-07-09 Yes NULL 2014-07-10 Yes NULL 2014-07-11 No NULL

The output will be

DATE LEAVEDAYS NXTWRKDAYS 2014-07-01 No 2014-07-02 2014-07-02 No 2014-07-03 2014-07-03 No 2014-07-04 2014-07-04 No 2014-07-05 2014-07-05 Yes 2014-07-08 2014-07-06 Yes 2014-07-08 2014-07-07 Yes 2014-07-08 2014-07-08 No 2014-07-09 2014-07-09 Yes 2014-07-11 2014-07-10 Yes 2014-07-11 2014-07-11 No 2014-07-12

As can be figure out that after 2014-07-04, the next working day was '2014-07-08' and henceforth the output after 2014-07-05 was 2014-07-08. after 2014-07-10, the next working day was '2014-07-11' and henceforth the output after 2014-07-10 was 2014-07-11

Script

declare @t table([DATE] DATE,LEAVEDAYS Varchar(10),NXTWRKDAYS DATE) insert into @t Values ('2014-07-01','No',NULL), ('2014-07-02','No',NULL), ('2014-07-03','No',NULL), ('2014-07-04','No',NULL), ('2014-07-05','Yes',NULL), ('2014-07-06','Yes',NULL), ('2014-07-07','Yes',NULL), ('2014-07-08','No',NULL), ('2014-07-09','Yes',NULL), ('2014-07-10','Yes',NULL), ('2014-07-11','No',NULL) select * from @t

最满意答案

检查此查询，希望它可以帮助您：

您的create table和insert脚本：

declare @t table([DATE] DATE,LEAVEDAYS Varchar(10),NXTWRKDAYS DATE) insert into @t Values ('2014-07-01','No',NULL), ('2014-07-02','No',NULL), ('2014-07-03','No',NULL), ('2014-07-04','No',NULL), ('2014-07-05','Yes',NULL), ('2014-07-06','Yes',NULL), ('2014-07-07','Yes',NULL), ('2014-07-08','No',NULL), ('2014-07-09','Yes',NULL), ('2014-07-10','Yes',NULL), ('2014-07-11','No',NULL)

要求输出：

SELECT t.date AS Date, t.leavedays AS LeaveDays, CASE WHEN t.LEAVEDAYS = 'No' THEN DATEADD(day,1, t.DATE) WHEN t.LEAVEDAYS = 'Yes' THEN (Select top 1 t1.date from @t t1 WHERE t1.date > t.DATE and t1.LEAVEDAYS = 'No' ORDER BY t1.date) ELSE null END AS NxtWorkingDay FROM @t t

Check this query, hope it helps you:

Your create table and insert script:

declare @t table([DATE] DATE,LEAVEDAYS Varchar(10),NXTWRKDAYS DATE) insert into @t Values ('2014-07-01','No',NULL), ('2014-07-02','No',NULL), ('2014-07-03','No',NULL), ('2014-07-04','No',NULL), ('2014-07-05','Yes',NULL), ('2014-07-06','Yes',NULL), ('2014-07-07','Yes',NULL), ('2014-07-08','No',NULL), ('2014-07-09','Yes',NULL), ('2014-07-10','Yes',NULL), ('2014-07-11','No',NULL)

Required Output:

SELECT t.date AS Date, t.leavedays AS LeaveDays, CASE WHEN t.LEAVEDAYS = 'No' THEN DATEADD(day,1, t.DATE) WHEN t.LEAVEDAYS = 'Yes' THEN (Select top 1 t1.date from @t t1 WHERE t1.date > t.DATE and t1.LEAVEDAYS = 'No' ORDER BY t1.date) ELSE null END AS NxtWorkingDay FROM @t t