Merge overlapping dates in SQL Server

Question

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

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 contains 1 column of number values from 0 to 1 000 000)

Output :

   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

Post (add to your question by using [Edit]) your best attempt to solve the issue and explain what's incorrect about the result it produces. — PM 77-1, Jan 24 '18 at 14:44
Also, please clarify whether the endpoints are exclusive or inclusive and what should happen if one period ends at the exact moment that another period starts - should they be merged or treated as separate periods? — Damien_The_Unbeliever, Jan 24 '18 at 14:46

score 7 · Accepted Answer · edited Nov 12 '19 at 16:44

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;

Merge overlapping dates in SQL Server

1 Answers1

Linked