Condense Time Periods with SQL

Question

I have a large data set which for the purpose of this question has 3 fields:

• Group Identifier
• From Date
• To Date

On any given row the From Date will always be less than the To Date but within each group the time periods (which are in no particular order) represented by the date pairs could overlap, be contained one within another, or even be identical.

What I'd like to end up with is a query that condenses the results for each group down to just the continuous periods. For example a group that looks like this:

| Group ID | From Date  | To Date    |
--------------------------------------
| A        | 01/01/2012 | 12/31/2012 |
| A        | 12/01/2013 | 11/30/2014 |
| A        | 01/01/2015 | 12/31/2015 |
| A        | 01/01/2015 | 12/31/2015 |
| A        | 02/01/2015 | 03/31/2015 |
| A        | 01/01/2013 | 12/31/2013 |

Would result in this:

| Group ID | From Date  | To Date    |
--------------------------------------
| A        | 01/01/2012 | 11/30/2014 |
| A        | 01/01/2015 | 12/31/2015 |

I've read a number of articles on date packing but I can't quite figure out how to apply that to my data set.

How can construct a query that would give me those results?

Answer 1

The solution from book "Microsoft® SQL Server ® 2012 High-Performance T-SQL Using Window Functions"

;with C1 as(
select GroupID, FromDate as ts, +1 as type, 1 as sub
  from dbo.table_name
union all
select GroupID, dateadd(day, +1, ToDate) as ts, -1 as type, 0 as sub
  from dbo.table_name),
C2 as(
select C1.*
     , sum(type) over(partition by GroupID order by ts, type desc
                      rows between unbounded preceding and current row) - sub as cnt
  from C1),
C3 as(
select GroupID, ts, floor((row_number() over(partition by GroupID order by ts) - 1) / 2 + 1) as grpnum
  from C2
  where cnt = 0)

select GroupID, min(ts) as FromDate, dateadd(day, -1, max(ts)) as ToDate
  from C3
  group by GroupID, grpnum;

Create table:

if object_id('table_name') is not null
  drop table table_name
create table table_name(GroupID varchar(100), FromDate datetime,ToDate datetime)
insert into table_name
select 'A', '01/01/2012', '12/31/2012' union all
select 'A', '12/01/2013', '11/30/2014' union all
select 'A', '01/01/2015', '12/31/2015' union all
select 'A', '01/01/2015', '12/31/2015' union all
select 'A', '02/01/2015', '03/31/2015' union all
select 'A', '01/01/2013', '12/31/2013'