SQL: Maximum possible date range by group

Question

I am using T-SQL (Microsoft SQL Server Management Studio 2017) and I have a data set that resembles the following:

BEGINDATE   ENDDATE     ID
2015-07-01  2015-07-12  1
2015-07-01  2015-07-12  1
2015-07-11  2015-07-15  1
2015-07-18  2015-08-04  1
2015-06-28  2015-07-04  2
2015-06-28  2015-07-03  2
2015-06-29  2015-07-04  2
2015-07-03  2015-07-15  2
2015-07-17  2015-07-20  2

What I would like to do is to merge overlapping dates by ID (There are a few examples out there that do this but not by group - like this one).

Ideally, the end result would be something like:

BEGINDATE   ENDDATE     ID
2015-07-01  2015-07-15  1
2015-07-18  2015-08-04  1
2015-06-28  2015-07-15  2
2015-07-17  2015-07-20  2

Any suggestions?

On what criteria you want to group it ? because your desired result is ambiguous. — zarruq, Aug 21 '17 at 16:22
This is a gaps and islands problem. Check here https://blogs.msdn.microsoft.com/samlester/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist/ — Juan Carlos Oropeza, Aug 21 '17 at 16:43

SqlZim · Accepted Answer · 2017-08-21T17:13:15.627

Using an adhoc calendar table for a gaps-and-islands solution:

declare @fromdate date, @thrudate date;
select  @fromdate = min(begindate), @thrudate = max(enddate) from t;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                 cross join n as tenK cross join n as hundredK
   order by [Date]
)
, cte as (
select
    t.id
  , d.date
  , grp = row_number() over (partition by t.id order by d.date)
        - datediff(day,@fromdate,d.date)
from dates d
  inner join t
    on d.date >= t.begindate
   and d.date <= t.enddate
group by t.id, d.date
)
select 
    BeginDate = min(cte.date)
  , EndDate   = max(cte.date)
  , id
from cte
where id is not null
group by id, grp
order by id, BeginDate

rextester demo: http://rextester.com/NKGY7104

returns:

+------------+------------+----+
| BeginDate  |  EndDate   | id |
+------------+------------+----+
| 2015-07-01 | 2015-07-15 |  1 |
| 2015-07-18 | 2015-08-04 |  1 |
| 2015-06-28 | 2015-07-15 |  2 |
| 2015-07-17 | 2015-07-20 |  2 |
+------------+------------+----+

@Strawberry I apologize, I adapted the referenced solution without enough scrutiny. I replaced that answer with this one. — SqlZim, Aug 21 '17 at 17:14

SQL: Maximum possible date range by group

1 Answers1