在SQL Server中合并重叠的日期 [英] Merge overlapping dates in SQL Server
本文介绍了在SQL Server中合并重叠的日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含3列的表:
I have a table that contains 3 columns :
Name | Datetime_Start | Datetime_End
A | 2017-01-02 00:00 | 2017-03-28 00:10
A | 2017-05-14 23:50 | 2017-05-29 23:50
B | 2017-05-18 00:00 | 2017-05-18 04:00
B | 2017-05-18 02:00 | 2017-05-18 03:00
C | 2017-01-02 00:00 | 2017-01-17 15:50
C | 2017-01-14 03:50 | 2017-01-28 15:50
我希望输出像这样,(基本上合并
I would like the output to be like this,(basically merge overlapping periods into one):
Name | Datetime_Start | Datetime_End
A | 2017-01-02 00:00 | 2017-03-28 00:10
A | 2017-05-14 23:50 | 2017-05-29 23:50
B | 2017-05-18 00:00 | 2017-05-18 04:00
C | 2017-01-02 00:00 | 2017-01-28 15:50
我尝试使用此处的建议:消除并减少重叠的日期范围
I tried to use what's suggested here : Eliminate and reduce overlapping date ranges
但是我的结果未正确合并,我认为这是由于我的datetime值的时间部分所致。
But my results are not merged correctly and I think it's due to the time part of my datetime values...
如果一个期间在另一个准确的时刻结束,则应合并句点。
If a period ends at the exact moment the other one starts, the periods should be merged.
select Name, Min(NewStartDate) Datetime_Start, MAX(Datetime_End) Datetime_End
from
(
select *,
NewStartDate = t.Datetime_Start+n.number,
NewStartDateGroup =
dateadd(d,
1- DENSE_RANK() over (partition by Name order by t.Datetime_Start+n.number),
t.Datetime_Start+n.number)
from Mytable t
inner join dbo.Numbers n
on n.number <= DATEDIFF(d, Datetime_Start, Datetime_End)
) X
group by Name, NewStartDateGroup
(dbo.Numbers包含1列从0到1000000的数值)
(dbo.Numbers contains 1 column of number values from 0 to 1 000 000)
输出:
Name | Datetime_Start | Datetime_End
A | 2017-11-04 00:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-04 23:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-05 00:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-05 23:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-06 23:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-07 00:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-07 23:10:00.000 | 2017-12-05 15:10:00.000
A | 2017-11-08 00:10:00.000 | 2017-12-05 15:10:00.000
推荐答案
SQL DEMO
declare @t table (Name varchar(100), Datetime_Start datetime, Datetime_End datetime);
insert into @t values
('A' , '2017-01-02 00:00' , '2017-03-28 00:10'),
('A' , '2017-05-14 23:50' , '2017-05-29 23:50'),
('B' , '2017-05-18 00:00' , '2017-05-18 04:00'),
('B' , '2017-05-18 02:00' , '2017-05-18 03:00'),
('C' , '2017-01-02 00:00' , '2017-01-17 15:50'),
('C' , '2017-01-14 03:50' , '2017-01-28 15:50');
with Datetime_Starts as
(
select distinct name, Datetime_Start
from @t as t1
where not exists
(select * from @t as t2
where t2.name = t1.name
and t2.Datetime_Start < t1.Datetime_Start
and t2.Datetime_End >= t1.Datetime_Start)
),
Datetime_Ends as
(
select distinct name, Datetime_End
from @t as t1
where not exists
(select * from @t as t2
where t2.name = t1.name
and t2.Datetime_End > t1.Datetime_End
and t2.Datetime_Start <= t1.Datetime_End)
)
select name, Datetime_Start,
(select min(Datetime_End)
from Datetime_Ends as e
where e.name = s.name
and Datetime_End >= Datetime_Start) as Datetime_End
from Datetime_Starts as s;
这篇关于在SQL Server中合并重叠的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文