sql-server - 在 SQL Server 中合并重叠日期

Question

我有一个包含 3 列的表:

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

我希望输出是这样的，(基本上将重叠的句点合并为一个):

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

我尝试使用此处建议的内容:Eliminate and reduce overlapping date ranges

但我的结果没有正确合并，我认为这是由于我的日期时间值的时间部分...

如果一个句点在另一个句点开始的确切时刻结束，则应合并句点。

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 到 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

Answer 1

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;