Sample data.
DECLARE @SourceData TABLE
(
ReferralKey INT NOT NULL
,PersonID INT NOT NULL
,ReferralStartDate DATETIME NOT NULL --Time Element Doesn't Matter so could use DATE
,ReferralEndDate DATETIME NULL --Time Element Doesn't Matter so could use DATE
,EpisodeKey INT NULL -- What I want to populate at the end
);
INSERT INTO @SourceData
(
ReferralKey,
PersonID,
ReferralStartDate,
ReferralEndDate
)
VALUES
(1,5, '2007-12-03', '2011-08-26'), -- Closed Referral
(2,5, '2013-05-02', NULL), -- Currently Open Referral
(3,5, '2013-05-03', NULL), -- Currently Open Referral
(4,5, '2013-05-03', '2016-01-01'), -- Same date start, different referral
(5,30, '2006-10-02', '2011-01-16'),
(6,30, '2011-01-17', '2012-08-12'),
(8,30, '2012-08-13', NULL),
(8,66, '2007-09-24', NULL),
(9,66, '2007-09-24', NULL)
;
SELECT * FROM @SourceData
My ask is to return the data into Episodes if the date ranges overlap or are adjacent to the following day.
Expected Results:
| PersonId | ReferralStartDate | ReferralEndDate |
|---|---|---|
| 5 | 2007-12-03 | 2011-08-26′ |
| 5 | 2013-05-02 | NULL |
| 30 | 2006-10-02 | NULL |
| 66 | 2007-09-24 | NULL |
3
select PersonID,
min(ReferralStartDate) as ReferralStartDate, nullif(max(ReferralEndDate), '9999-12-30') as ReferralEndDate
from (
select ReferralKey, PersonID, ReferralStartDate, coalesce(ReferralEndDate, '9999-12-30') as ReferralEndDate,
sum(tomerge) over(partition by PersonID order by ReferralStartDate, ReferralEndDate) as grp
from (
select
ReferralKey, PersonID, ReferralStartDate, ReferralEndDate,
case when
ReferralStartDate
<=
lag(max_so_far) over(partition by PersonID order by ReferralStartDate, ReferralEndDate) + 1
then 0 else 1 end as tomerge
from (
select ReferralKey, PersonID, ReferralStartDate, ReferralEndDate,
max( coalesce(ReferralEndDate, '9999-12-30') ) over(partition by PersonID order by ReferralStartDate, ReferralEndDate)
as max_so_far
from @SourceData
) d1
) d2
) d3
group by PersonID, grp
order by PersonID, ReferralStartDate
;
https://dbfiddle.uk/j4AboS6R
1