1

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
  • You cannot reset the counter, have you considered to use a cursor? – McNets Mar 12 at 17:56
1
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

| improve this answer | |
  • This solution worked great! Thank you for your quick help! I would just know that the from FROM should be updated from ELIGIBILITY TO @ELIGIBILITY. – JOSH W Mar 12 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.