找到日期范围的差距 - TSQL
我有以下的SQL,我想在以下日期的差距。找到日期范围的差距 - TSQL
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年5月1日至2017年5月4日和2017年5月19日至2017年5月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, 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
编辑
它从四个查询收集数据,并做了union all
。
第一查询:
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
选择最大的日期放在桌子上,@enddate
(@startdate
和@enddate
之间最大)之间的差距,如果有一定的差距。
所有这些记录都插入到@final_result
表中,以便它们可以按间隔排序。
您可以尝试下面的代码。我从@StartDate遍历到@endDate并找到差距。
declare @startdate datetime = '2017-05-01'
declare @enddate datetime = '2017-05-04'
declare @startdate1 datetime, @enddate1 datetime
declare @dates table (date1 date,date2 date)
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
set @[email protected]
while @startdate1<[email protected]
begin
if not exists(select 1 from #tmpdates where @startdate1 between date1 and date2)
begin
if not exists (select 1 from @dates where @startdate1 > date1 and date2 is null)
begin
insert into @dates(date1)values(@startdate1)
end
else
begin
if @startdate1+1>[email protected]
begin
update @dates set [email protected] where date2 is null
end
set @startdate1+=1
end
end
else
begin
update @dates set [email protected] where date2 is null
end
set @startdate1+=1
end
select * from
(select date1,date2, rate from #tmpdates
union
select *,0 as rate from @dates
) A WHERE date1>[email protected] and date2<[email protected]
drop table #tmpdates
请使用以下查询:
DECLARE @STARTDATE DATE = '2017-05-01'
DECLARE @ENDDATE DATE = '2017-05-25'
DECLARE @DATES TABLE (ID INT, DATE1 DATE, DATE2 DATE, RATE INT)
INSERT INTO @DATES VALUES
(1, '2017-05-05', '2017-05-15', 10),
(2, '2017-05-16', '2017-05-19', 12),
(3, '2017-05-21', '2017-05-25', 15)
SELECT* FROM
(
SELECT @STARTDATE AS DATE1,DATEADD(DAY,-1,MIN(DATE1)) AS DATE2,'NO DATA'AS RATE FROM @DATES
UNION
SELECT
CASE WHEN
LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL
ELSE DATEADD(DAY,1,DATE2) END AS DATE1,
CASE WHEN
LEAD(DATE1) OVER (ORDER BY DATE1) = DATEADD(DAY,1,DATE2) THEN NULL
ELSE LEAD(DATEADD(DAY,-1,DATE1)) OVER (ORDER BY DATE1) END AS DATE2,
'NO DATA'AS RATE
FROM @DATES d
UNION
SELECT DATE1, DATE2,CAST(RATE AS NVARCHAR(10)) FROM @DATES
UNION
SELECT DATEADD(DAY,1,MAX(DATE2)) AS DATE1,@ENDDATE AS DATE2,'NO DATA'AS RATE FROM @DATES
) A WHERE A.DATE2 IS NOT NULL AND A.DATE1 <= A.DATE2
AND DATE1 >= @STARTDATE AND DATE2 <[email protected]
ORDER BY A.DATE1
如@startdate ='2017-05-01''和'@enddate ='2017-05-04'',这不会返回一行,因为@ k-s需要。 – ahoxha
嗨@ahoxha,好点。我刚刚修改了代码。你现在可以检查一下。 :) –
我真的无法理解你想在这里实现什么。你可以试着再解释一遍吗? –
OP有一个开始日期和一个结束日期加上一个包含日期范围的表。他想要查找表中不存在的开始日期和结束日期之间的范围。 – Tanner
@Tanner这还不够。为什么1-4和19-20,而不是17和21-24? –