Sql-server – Merging Continous Date Ranges with upto 63 days in between

aggregategaps-and-islandssql server

I am trying to write a query that will take multiple date ranges and combine them into a contiguous date range. The catch is that I have to allow for up to 63 days in between the last termination date and the next effective date. I have found other queries that allow for one day in between but not multiple days.

DECLARE @ELIGIBILITY  TABLE
(
EFF_DT DATETIME,
TERM_DT DATETIME,
ID1 INT,
ID2 INT
)

INSERT INTO @ELIGIBILITY
(EFF_DT, TERM_DT, ID1, ID2) VALUES
--EFF_DT, TERM_DT, ID1, ID2
('25 MAY 1990','30 NOV 1994',18,36), -- 1 DAY GAP 
('01 DEC 1994','31 DEC 1994',18,36), -- 1 DAY GAP
('01 JAN 1995','11 FEB 1995',18,36), -- 1 DAY GAP
('05 APR 1995', '31, DEC 1995',18,36), -- 53 DAY GAP
('16 JUN 2002','20 APR 2007',18,36), -- 2539 DAY GAP
('16 JUN 2002','20 APR 2007',12,3),
('21 JUL 2007','28 JUL 2007',12,3), -- 92 DAY GAP
('08 AUG 2007','31 AUG 2007',12,3), -- 11 DAY GAP
('11 OCT 2007','31 DEC 2007',12,3), -- 41 DAY GAP
('01 JAN 2008','07 JAN 2008',12,3), -- 1 DAY GAP
('14 JAN 2008','21 JAN 2008',12,3), -- 7 DAY GAP
('01 JUN 2008','30 SEP 2008',12,3), -- 5 MONTH GAP
('31 OCT 2008','30 NOV 2008',12,3), -- 30 DAY GAP
('01 JAN 2009','31 JAN 2009',12,3), -- 31 DAY GAP
('04 MAR 2009','31 MAR 2009',12,3), -- 32 DAY GAP
('03 MAY 2009','31 MAY 2009',12,3), -- 33 DAY GAP
('04 JUL 2009','31 JUL 2009',12,3)  -- 34 DAY GAP

EXPECTED RESULTS for 18,36

5/25/1990 - 12/31/1995
6/16/2002 - 4/20/2007

EXPECTED RESULTS for 12,3

6/16/2002 - 4/20/2007
7/21/2007 - 1/21/2008
6/1/2008 - 7/31/2009

Best Answer

WITH
CTE1 AS (SELECT LAG(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) PREV_DT,
                EFF_DT,
                TERM_DT,
                LEAD(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) NEXT_DT,
                ID1, 
                ID2
         FROM ELIGIBILITY),
CTE2 AS (SELECT EFF_DT,
                TERM_DT,
                ID1, 
                ID2, 
                COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) D1, 
                COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) D2
         FROM CTE1
         WHERE COALESCE(DATEDIFF( DAY, PREV_DT, EFF_DT), 1000) > 63 
            OR COALESCE(DATEDIFF(DAY, TERM_DT, NEXT_DT), 1000) > 63)
SELECT DISTINCT
       CASE WHEN D1 > 63 
            THEN EFF_DT
            ELSE LAG(EFF_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) 
            END EFF_DT,
       CASE WHEN D2 > 63 
            THEN TERM_DT
            ELSE LEAD(TERM_DT) OVER (PARTITION BY ID1,ID2 ORDER BY EFF_DT) 
            END TERM_DT,
       ID1,
       ID2
FROM CTE2
ORDER BY ID1, ID2, EFF_DT

fiddle