Compare dates in different columns and rows dynamically in SQL

Question

I have a set of data like this.

Data

ID  Start_dt    End_dt
A   1/1/2010    12/31/2010
A   1/1/2011    12/31/2011
A   6/1/2012    12/31/2012
A   1/1/2014    12/31/2014
A   1/1/2016    10/31/2016
A   1/1/2018    12/31/2018
B   1/1/2016    2/29/2016
B   3/1/2016    10/31/2016
B   1/1/2017    7/31/2017
B   1/1/2019    12/31/9999
C   1/1/2017    12/31/2017
C   1/1/2017    12/31/2018
C   1/1/2019    12/31/9999

I need to create a query that looks at each member's row, compares the current Start_dt against the previous End_dt. If the difference is less than one year, treat those 2 records as one continuous enrollment and return the combined MIN Start_dt and MAX End_dt, and repeat that for all rows for each member. If the difference is >=1 year, treat that as separate enrollment.

Desired result

ID  Start_dt    End_dt
A   1/1/2010    12/31/2012
A   1/1/2014    12/31/2014
A   1/1/2016    10/31/2016
A   1/1/2018    12/31/2018
B   1/1/2016    7/31/2017
B   1/1/2019    12/31/2019
C   1/1/2017    12/31/9999

Here's a Create Table query:

if OBJECT_ID ('tempdb..#test1') is not null
drop table #test1
CREATE TABLE #test1 (
    ID varchar(10),
    Start_dt datetime,
    End_dt datetime
);

INSERT INTO #test1 VALUES ('A', '1/1/2010', '12/31/2010')
,('A', '1/1/2011', '12/31/2011')
,('A', '6/1/2012', '12/31/2012')
,('A', '1/1/2014', '12/31/2014')
,('A', '1/1/2016', '10/31/2016')
,('A', '1/1/2018', '12/31/2018')

,('B', '1/1/2016', '2/29/2016')
,('B', '3/1/2016', '10/31/2016')
,('B', '1/1/2017', '7/31/2017')
,('B', '1/1/2019', '12/31/9999')

,('C', '1/1/2017', '12/31/2017')
,('C', '1/1/2017', '12/31/2018')
,('C', '1/1/2019', '12/31/2999')

I've been trying to solve this for days but have tried self-joins, loops but have not found a good solution. Can someone help?

Thank you!

Gordon Linoff · Accepted Answer · 2019-03-19 18:08:44Z

1

You can use lag() or a cumulative max() to get the previous end date. Then compare it to the current start date.

When the difference is more than a year, then a new group starts. Do a cumulative sum of these new group starts to get a grouping id.

And the rest is aggregation:

select id, min(start_dt), max(end_dt)
from (select t1.*,
             sum(case when prev_end_dt > dateadd(year, -1, start_dt) then 0 else 1 end) over
                 (partition by id order by start_dt) as grp
      from (select t1.*,
                   max(end_dt) over (partition by id
                                      order by start_dt
                                      rows between unbounded preceding and 1 preceding
                                     ) as prev_end_dt
            from test1 t1
           ) t1
     ) t1
group by id, grp
order by id, min(start_dt);

answered Mar 19, 2019 at 18:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Henry Over a year ago

Wow! That worked perfectly. Thank you so much! You just saved me hours of hair-pulling. For my education, what does this clause do "rows between unbounded preceding and 1 preceding"?

Eric · Accepted Answer · 2019-03-19 18:39:32Z

0

You could try this query

SELECT ID, StartDate, End_dt AS EndDate
FROM (
    SELECT * 
        , LAG(End_dt) OVER(PARTITION BY ID ORDER BY ID, Start_dt, End_dt) AS PrevEnd
        , DATEDIFF(DAY, LAG(End_dt) OVER(PARTITION BY ID ORDER BY ID, Start_dt, End_dt), Start_dt) AS DaysBreak
        , (
            CASE
                WHEN DATEDIFF(DAY, LAG(End_dt) OVER(PARTITION BY ID ORDER BY ID, Start_dt, End_dt), Start_dt) > 365 THEN Start_dt
                WHEN LAG(End_dt) OVER(PARTITION BY ID ORDER BY ID, Start_dt, End_dt) IS NULL THEN Start_dt
                ELSE NULL
            END
        ) AS StartDate
    FROM #test1
) a
WHERE StartDate IS NOT NULL

answered Mar 19, 2019 at 18:39

Eric

3,2701 gold badge22 silver badges27 bronze badges

1 Comment

Henry Over a year ago

Thanks! This is helpful but didn't product the correct MAX(End_dt) I was looking for. If I combine your lag() function with Gordon's aggregation method above, it works. You showed me how to use lag(). I couldn't upvote you because I'm new but I appreciate your help.

Collectives™ on Stack Overflow

Compare dates in different columns and rows dynamically in SQL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related