drew.allen (12/8/2016)
Peter Brinkhaus (12/8/2016)
You have to test it yourself if it performs better, but here's simplified alternative:
SELECT
Q.memid,
Q.EffectiveDate,
Q.termdate,
MIN(Q.EffEctiveDate) OVER (PARTITION BY Q.memid, Q.GroupingDate) sincefrom,
MAX(Q.termdate) OVER (PARTITION BY Q.memid, Q.GroupingDate) tildate
FROM
(
SELECT
M.memid, M.EffectiveDate, M.termdate,
DATEADD(dd, -SUM(DATEDIFF(dd, M.EffectiveDate, M.termdate) + 1) OVER
(PARTITION BY M.memid
ORDER BY M.EffectiveDate
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), termdate) GroupingDate
FROM
#ME M
) Q
Edit: added Q.memid to PARTITION BY
This solution doesn't work if there are overlaps like the following data set.
create table #ME (memid int , EffectiveDate datetime , termdate datetime)
Insert into #ME values ('123','3-Dec-16','10-Jan-17')
Insert into #ME values ('123','11-Jan-17','8-Feb-17')
Insert into #ME values ('123','7-Feb-17','5-Mar-17')
Insert into #ME values ('123','8-Mar-17','15-Apr-17')
Insert into #ME values ('123','16-Apr-17','24-May-17')
Here is a solution that will work with overlaps and performs the same on this small data set.
;
WITH C1 AS (
SELECT *, MAX(DATEADD(DAY, 1, m.termdate)) OVER(PARTITION BY m.memid ORDER BY m.EffectiveDate, m.termdate ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prevend
FROM #ME m
)
, C2 AS (
SELECT *, SUM(s.is_start) OVER(PARTITION BY C1.memid ORDER BY C1.EffectiveDate, C1.termdate ROWS UNBOUNDED PRECEDING) AS grp
FROM C1
CROSS APPLY ( VALUES(CASE WHEN C1.EffectiveDate <= C1.prevend THEN 0 ELSE 1 END ) ) AS s(is_start)
)
SELECT C2.memid, C2.EffectiveDate, C2.termdate, MIN(C2.EffectiveDate) OVER(PARTITION BY grp) AS start_dt, MAX(C2.termdate) OVER(PARTITION BY grp) AS end_dt
FROM C2
;
This is based on the article New Solution to the Packing Intervals Problem
Drew
Thanks for sharing that link, Drew. I'd been messing with LAG() for a while when you posted it up, and the solutions are virtually identical:
-- Original LAG query
SELECT memid, EffectiveDate, termdate,
SinceFrom = MIN(EffectiveDate) OVER (PARTITION BY memid, grp),
TilDate = MAX(termdate) OVER (PARTITION BY memid, grp)
FROM (
SELECT *, grp = SUM(x.Flag) OVER (PARTITION BY memid ORDER BY EffectiveDate, termdate ROWS UNBOUNDED PRECEDING)
FROM (
SELECT *, LastEndDate = DATEADD(DAY,1,LAG(Termdate,1) OVER(PARTITION BY memid ORDER BY EffectiveDate, termdate))
FROM #ME
) d
CROSS APPLY (SELECT Flag = CASE WHEN EffectiveDate <= LastEndDate THEN 0 ELSE 1 END) x
) e
-- Modified to match Drew's CTE format
;WITH c1 AS (
SELECT *, LastEndDate = DATEADD(DAY,1,LAG(Termdate,1) OVER(PARTITION BY memid ORDER BY EffectiveDate, termdate))
FROM #ME
),
c2 AS (
SELECT *, grp = SUM(x.Flag) OVER (PARTITION BY memid ORDER BY EffectiveDate, termdate ROWS UNBOUNDED PRECEDING)
FROM c1
CROSS APPLY (SELECT Flag = CASE WHEN EffectiveDate <= LastEndDate THEN 0 ELSE 1 END) x
)
SELECT memid, EffectiveDate, termdate, SinceFrom = MIN(EffectiveDate) OVER (PARTITION BY memid, grp), TilDate = MAX(termdate) OVER (PARTITION BY memid, grp)
FROM c2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden