Grouping Overlapping Date Ranges

1

I have some data that needs to be merged when dates overlap before I can run a process. Each record contains a memberID (INT), Value1 (INT), StartDate (Date), EndDate(Date). In any case when dates overlap for a member, I merge these into 1 record, which has the sum of Value1 and adjusted StartDate and EndDate.
Example1:
MemberID, Value1, StartDate, EndDate
201 4 2015-6-20 2015-6-22
201 2 2015-6-22 2015-6-24

would need to become:
MemberID, Value1, StartDate, EndDate
201 6 2015-6-20 2015-6-24

Example2:
MemberID, Value1, StartDate, EndDate
201 0 2015-7-15 2015-7-16
201 2 2015-7-18 2015-7-22
201 2 2015-7-21 2015-7-22

should become
MemberID, Value1, StartDate, EndDate
201 0 2015-7-15 2015-7-16
201 4 2015-7-18 2015-7-22

Anyone have any ideas on how I should go about this?
Thanks.

2 
; with data (MemberID, Value1, StartDate, EndDate) as
(
    select    200, 1, '2015-7-05', '2015-7-10' union all
    select    200, 2, '2015-7-11', '2015-7-12' union all
    select    201, 0, '2015-7-15', '2015-7-16' union all
    select    201, 2, '2015-7-18', '2015-7-22' union all
    select    201, 2, '2015-7-21', '2015-7-22' union all
    select    201, 3, '2015-7-24', '2015-7-28' union all
    select    201, 4, '2015-7-28', '2015-7-31'
),
cte as
(
    select    rn = row_number() over (partition by MemberID order by StartDate), *
    from    data
),
rcte as
(
    select    rn, MemberID, Value1, StartDate, EndDate,
        grp = 1, grpStart = StartDate, grpEnd = EndDate
    from    cte
    where    rn    = 1

    union all

    select    c.rn, c.MemberID, c.Value1, c.StartDate, c.EndDate,
        grp     = case when c.StartDate between r.grpStart and r.grpEnd
                then r.grp
                else r.grp + 1
                end,
        grpStart = r.grpStart,
        grpEnd   = case when c.EndDate > r.grpEnd then c.EndDate else r.grpEnd end
    from    rcte r
        inner join cte c    on    r.MemberID    = c.MemberID
                    and    r.rn        = c.rn - 1
)
select    MemberID, Value1 = sum(Value1), StartDate = min(grpStart), EndDate = max(grpEnd)
from    rcte
group by MemberID, grp
order by MemberID
3

This is close. Everything looks like it is working, except it is returning the first StartDate for all records for each member.

4

Fixed

; with data (MemberID, Value1, StartDate, EndDate) as
(
    select    200, 1, '2015-7-05', '2015-7-10' union all
    select    200, 2, '2015-7-11', '2015-7-12' union all
    select    201, 0, '2015-7-15', '2015-7-16' union all
    select    201, 2, '2015-7-18', '2015-7-22' union all
    select    201, 2, '2015-7-21', '2015-7-22' union all
    select    201, 3, '2015-7-24', '2015-7-28' union all
    select    201, 4, '2015-7-28', '2015-7-31'
),
cte as
(
    select    rn = row_number() over (partition by MemberID order by StartDate), *
    from    data
),
rcte as
(
    select    rn, MemberID, Value1, StartDate, EndDate,
        grp = 1, grpStart = StartDate, grpEnd = EndDate
    from    cte
    where   rn    = 1

    union all

    select  c.rn, c.MemberID, c.Value1, c.StartDate, c.EndDate,
        grp     = case     when c.StartDate between r.grpStart and r.grpEnd
                then r.grp
                else r.grp + 1
                end,
        grpStart = case when c.StartDate between r.grpStart and r.grpEnd
                then r.grpStart
                else c.StartDate
                end,
        grpEnd   = case when c.EndDate > r.grpEnd then c.EndDate else r.grpEnd end
    from    rcte r
        inner join cte c    on    r.MemberID    = c.MemberID
                    and    r.rn        = c.rn - 1
)
select    MemberID, Value1 = sum(Value1), StartDate = min(grpStart), EndDate = max(grpEnd)
from    rcte
group by MemberID, grp
1 Like
5

That did it. Thank You!