Sql Server 2014 Merging Overlapping Date Ranges

I have a table with 200.000 rows in a SQL Server 2014 database looking like this: CREATE TABLE DateRanges ( Contract VARCHAR(8), Sector VARCHAR(8), StartDate DATE,

Solution 1:

This answer makes use of a few different techniques. The first is a recursive-cte that creates a table with every relevant cal_date which then gets cross apply'd with unique Contract values to get every combination of both values. The second is window-functions such as lag and row_number to determine a variety of things detailed in the comments below. Lastly, and probably most importantly, gaps-and-islands to determine when one Contract/Sector combination ends and the next begins.

Answer:

--determine range of dates declare@bgn_dt date= (selectmin(StartDate) from DateRanges)
    , @end_dt date= (selectmax(EndDate) from DateRanges)

--use a recursive CTE to create a record for each day / Contract
; with dates as
    (
        select@bgn_dt as cal_date
        unionallselect dateadd(d, 1, a.cal_date) as cal_date
        from dates as a
        where a.cal_date <@end_dt
    )
select d.cal_date
, c.Contract
into #contract_dates
from dates as d
cross apply (selectdistinct Contract from DateRanges) as c
option (maxrecursion 0)

--Final Selectselect f.Contract
, f.Sector
, min(f.cal_date) as StartDate
, max(f.cal_date) as EndDate
from (
    --Use the sum-over to obtain the Island Numbersselect dr.Contract
    , dr.Sector
    , dr.cal_date
    , sum(dr.IslandBegin) over (partitionby dr.Contract orderby dr.cal_date asc) as IslandNbr
    from (
        --Determine if the record is the start of a new Islandselect a.Contract
        , a.Sector
        , a.cal_date
        , casewhenlag(a.Sector, 1, NULL) over (partitionby a.Contract orderby a.cal_date asc) = a.Sector then0else1endas IslandBegin
        from (
            --Determine which Contract/Date combinations are valid, and rank the Sectors that are in effectselect cd.cal_date
            , dr.Contract
            , dr.Sector
            , dr.EndDate
            , row_number() over (partitionby dr.Contract, cd.cal_date orderby dr.StartDate desc) as ConractSectorRnk
            from #contract_dates as cd
            leftjoin DateRanges as dr on cd.Contract = dr.Contract
                                      and cd.cal_date between dr.StartDate and dr.EndDate
            ) as a
        where a.ConractSectorRnk =1and a.Contract isnotnull
        ) as dr
    ) as f
groupby f.Contract
, f.Sector
, f.IslandNbr
orderby f.Contract asc
, min(f.cal_date) asc

Output:

+----------+--------+------------+------------+|Contract|Sector|StartDate|EndDate|+----------+--------+------------+------------+|111|999|2014-01-01|2014-07-31||111|888|2014-08-01|2014-08-14||111|777|2014-08-15|2014-08-31||111|999|2014-09-01|2014-12-31||222|999|2014-01-01|2014-10-31||222|666|2014-11-01|2014-11-14||222|555|2014-11-15|2014-11-30||222|999|2014-12-01|2014-12-31|+----------+--------+------------+------------+