SQL: Creating Orignial Effective date from list of Date ranges

0 votes
asked Feb 23, 2017 by dnp2

I have the following data

0001  |A     |1/1/2015|6/1/2015
0001  |B     |6/1/2015|1/1/2017
0001  |C     |1/1/2017|11/17/1858
0002  |A     |1/1/2015|6/1/2015
0002  |B     |1/1/2016|1/1/2017
0002  |C     |1/1/2017|11/17/1858
0003  |A     |1/1/2017|2/1/2017
0003  |B     |2/1/2016|11/17/1858

From it I would like to extract the current group a member is on and the original start date of a member where the do not have a lapse in coverage. Results would be as follows.

0001  |C     |1/1/2015|11/17/1858
0002  |C     |1/1/2016|11/17/1858
0003  |B     |1/1/2017|11/17/1858

Member 0002 I want the start date of 1/1/2016 as they had a lapse in coverage from 6/1/2015 to 1/1/2016. As a note the 11/17/1858 is some arbitrary end date our system gives members who are currently active with no end date.

1 Answer

0 votes
answered Feb 23, 2017 by gordon-linoff

The following gets one row for each period of continuous membership:

select mbr_id,
       max(grp_id) keep (dense_rank first order by srt_date desc) as grp_id,
       min(srt_date) as srt_date,
       (case when min(end_date) = date '1858-11-17' then min(end_date)
             else max(end_date)
        end) as end_date
from (select t.*,
             sum(case when prev_end_date = srt_date then 0 else 1 end) over (partition by mbr_id order by srt_date) as grp
      from (select t.*,
                   lag(end_date) over (partition by mbr_id order by srt_date) as prev_end_date
            from t
           ) t
      ) t
group by mbr_id, grp;

You can filter for the end_date being active, if you want the data for the current memberships.

Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter