sql-server - 查找日期范围中的空白 - SQL

Question

我有以下 SQL，并且我想要以下日期之间的间隙。

declare @startdate datetime = '2017-05-01'
declare @enddate datetime = '2017-05-25'

create table #tmpdates (id int, date1 datetime, date2 datetime, rate int)

insert into #tmpdates values (1, '2017-05-05', '2017-05-15', 10)
insert into #tmpdates values (2, '2017-05-16', '2017-05-18', 12)
insert into #tmpdates values (3, '2017-05-21', '2017-05-25', 15)

select * from #tmpdates where date1 >= @startdate and date2 <= @enddate

drop table #tmpdates

因此输出应包含 2017-05-01 至 2017-05-04 和 2017-05-19 至 2017-05-20 - 另外 2 条记录。

Output:
1   5/1/2017 0:00   5/4/2017 0:00   NO DATA
2   5/5/2017 0:00   5/15/2017 0:00  10
3   5/16/2017 0:00  5/18/2017 0:00  12
4   5/19/2017 0:00  5/20/2017 0:00  NO DATA
5   5/21/2017 0:00  5/25/2017 0:00  15

在我的上述查询中，仅返回日期范围记录。请指导或者我如何也包含这些记录？

Answer 1

这是在没有重叠间隔的假设下工作的。

declare @startdate datetime = '2017-05-16'
declare @enddate datetime = '2017-05-26'

create table #tmpdates (id int, date1 datetime, date2 datetime, rate int)

insert into #tmpdates values (0, '2017-04-01', '2017-04-25',22)
insert into #tmpdates values (1, '2017-05-05', '2017-05-15', 10)
insert into #tmpdates values (2, '2017-05-16', '2017-05-18', 12)
insert into #tmpdates values (3, '2017-05-21', '2017-05-25', 15)

declare @final_result table (date1 date, date2 date, rate int)

insert into @final_result 

select @startdate,dateadd(day,-1,t.date1),null
from #tmpdates t
where @startdate < t.date1 and 
        t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate)

union all

select date1, date2, rate 
from #tmpdates 
where (date1 >= @startdate or date2 >= @startdate) and 
      (date2 <= @enddate or date1 <= @enddate)

union all

select dateadd(day,1,t.date2), 
        ( select dateadd(day,-1,min(t3.date1)) 
            from #tmpdates t3 where t3.date1 > t.date2) , 
        null
from #tmpdates t
where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates  t1 where t1.date1 > t.date2)
and t.date1 >= @startdate and t.date2 <= @enddate

union all

select dateadd(day,1,max(t.date2)), @enddate, null
from #tmpdates t
having max(t.date2) < @enddate


drop table #tmpdates

select * from @final_result order by date1

编辑

它从四个查询中收集数据并进行联合。

第一个查询:

select @startdate,dateadd(day,-1,t.date1),null
from #tmpdates t
where @startdate < t.date1 and 
        t.date1 <= (select min(t1.date1) from #tmpdates t1 where t1.date1 >= @startdate)

选择@startdate 和表中第一个(最小)日期之间的间隔，如果@startdate 之前有间隔，它们将被忽略。因此，它会选择从 @startdate 到大于 @startdate 的间隔的第一个日期的间隙(如果有)。

第二个查询:

select date1, date2, rate 
from #tmpdates 
where (date1 >= @startdate or date2 >= @startdate) and 
      (date2 <= @enddate or date1 <= @enddate)

从表中选择记录(无间隙)。如果 @startdate 落在该范围内，则包含该记录。 @enddate 参数也会发生同样的情况。

第三个​​查询:

select dateadd(day,1,t.date2), 
        ( select dateadd(day,-1,min(t3.date1)) 
            from #tmpdates t3 where t3.date1 > t.date2) , 
        null
from #tmpdates t
where dateadd(day,1,t.date2) < (select min(t1.date1) from #tmpdates  t1 where t1.date1 > t.date2)
and t.date1 >= @startdate and t.date2 <= @enddate

选择表格上最小和最大间隔(位于 @startdate 和 @enddate 之间)之间的间隙。

最后第四个查询:

select dateadd(day,1,max(t.date2)), @enddate, null
from #tmpdates t
having max(t.date2) < @enddate

选择表格中最大日期(@startdate 和 @enddate 之间的最大日期)与 @enddate 之间的差距(如果有)一个间隙。

所有这些记录都插入到@final_result表中，以便可以按时间间隔排序。