我有下面的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
解决方法
这在假设没有重叠间隔的情况下起作用.
declare @startdate datetime = '2017-05-16'
declare @enddate datetime = '2017-05-26'
create table #tmpdates (id int,rate int)
insert into #tmpdates values (0,'2017-04-01','2017-04-25',22)
insert into #tmpdates values (1,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,min(t3.date1))
from #tmpdates t3 where t3.date1 > t.date2),null
from #tmpdates t
where dateadd(day,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,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,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,rate
from #tmpdates
where (date1 >= @startdate or date2 >= @startdate) and
(date2 <= @enddate or date1 <= @enddate)
从表中选择记录(非间隙).如果@startdate在范围之间,则包含该记录.与@enddate参数相同.
第三个查询：
select dateadd(day,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,null from #tmpdates t having max(t.date2) < @enddate
选择表和@enddate之间的最大日期(@startdate和@enddate之间)之间的差距,如果有差距.
所有这些记录都插入到@final_result表中,以便它们可以按间隔排序.
