question

TheMac avatar image
TheMac asked

How to merge enrollment date spans from two tables

I have an interesting enrollment-span query that involves date ranges and values that need to be combined from two related tables, resulting in a table with contiguous date spans and “gaps” filled in for non-date values. Table 1 (E_TAB): MEM_ID ENROLL_EFF_DATE ENROLL_END_DATE PLAN_GROUP_ID PLAN_ID U110 2004-06-01 2015-03-22 125 5 U110 2015-03-23 2015-09-30 125 9 U110 2015-10-01 2100-12-31 125 5 Table 2 (P_TAB): MEM_ID PCP_EFF_DATE PCP_EXP_DATE PCP_ID U110 2004-06-14 2015-02-02 16297 U110 2015-02-03 2100-12-31 24557 Desired Outcome (O_TAB): MEM_ID EFF_DATE END_DATE PLAN_GROUP_ID PLAN_ID PCP_ID U110 2004-06-01 2015-02-02 125 5 16297 U110 2015-02-03 2015-03-22 125 5 24557 U110 2015-03-23 2015-09-30 125 9 24557 U110 2015-10-01 2100-12-31 125 5 24557 DDL and Data (constraints and indexes not shown for clarity): create table dbo.E_TAB ( MEM_ID varchar(50) not null , ENROLL_EFF_DATE date not null , ENROLL_END_DATE date not null , PLAN_GROUP_ID varchar(50) not null , PLAN_ID varchar(3) not null ) ; insert dbo.E_TAB values ('U110', '2004-06-01', '2015-03-22', '125', '5') , ('U110', '2015-03-23', '2015-09-30', '125', '9') , ('U110', '2015-10-01', '2100-12-31', '125', '5') -------------------------------------------------------- create table dbo.P_TAB ( MEM_ID varchar(50) not null , PCP_EFF_DATE date not null , PCP_EXP_DATE date not null , PCP_ID varchar(50) not null ) ; insert dbo.P_TAB values ('U110', '2004-06-14', '2015-02-02', '16297') , ('U110', '2015-02-03', '2100-12-31', '24557') ; -------------------------------------------------------- create table dbo.O_TAB ( MEM_ID varchar(50) not null , EFF_DATE date not null , END_DATE date not null , PLAN_GROUP_ID varchar(50) not null , PLAN_ID varchar(3) not null , PCP_ID varchar(50) not null ) ; I’d like to use a non-cursor solution, of course; any help would be appreciated.
ctedate-rangedate gaps
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@TheMac I added the answer, but it went into moderation for approval, should become available later today. Please let me know if it works for you. Thank you.
0 Likes 0 ·

1 Answer

·
Oleg avatar image
Oleg answered
Here is the script which works based on the following assumptions: 1. The very first enrollment (per member) must start from the enrollment date regardless of whether it is within the PCP date range or not. This explains why the EFF\_DATE of the first row in the results is 2004-06-01 even though the PCP range starts on 2004-06-14, so technically the member is not covered by any PCP for 13 days. 2. For other rows (per member) the EFF\_DATE depends on whether ENROLL\_EFF\_DATE is within the PCP date ranges. If yes then it is used for EFF\_DATE. Otherwise, the PCP_EFF_DATE is used to achieve continuity 3. If the ENROLL\_END\_DATE is earlier than PCP\_EXP\_DATE then it can be used for END\_DATE. Otherwise, the PCP\_EXP\_DATE is used. 4. The rows in the tables must match by MEM\_ID and also have either PCP\_EFF\_DATE within the enrollment dates range or the ENROLL\_EFF\_DATE within the range of the PCP effective date range. The script below uses the [LAG][1] analytical function which is available in SQL Server 2012 or newer. For older versions it does not exist but can be mimicked via numbering the rows partitioned by member and then joining the result with itself on the off-by-one basis. --insert into dbo.O_TAB select e.MEM_ID, -- EFF_DATE of the first enrollment cannot be earlier or later than e.ENROLL_EFF_DATE -- For any other rows the EFF_DATE is the e.ENROLL_EFF_DATE it the latter is within -- PCP effective date range, and p.PCP_EFF_DATE otherwise. case when lag(e.MEM_ID) over (partition by e.MEM_ID order by e.ENROLL_EFF_DATE) is null or e.ENROLL_EFF_DATE between p.PCP_EFF_DATE and p.PCP_EXP_DATE then e.ENROLL_EFF_DATE else p.PCP_EFF_DATE end EFF_DATE, case when e.ENROLL_END_DATE < p.PCP_EXP_DATE then e.ENROLL_END_DATE else p.PCP_EXP_DATE end END_DATE, e.PLAN_GROUP_ID, e.PLAN_ID, p.PCP_ID from dbo.E_TAB e inner join dbo.P_TAB p on e.MEM_ID = p.MEM_ID and ( -- either PCP effective date is within the enrollment period -- or enrollment start date is within the PCP effective period p.PCP_EFF_DATE between e.ENROLL_EFF_DATE and e.ENROLL_END_DATE or e.ENROLL_EFF_DATE between p.PCP_EFF_DATE and p.PCP_EXP_DATE ); Based on the sample data in question, the select produces the following result: MEM_ID EFF_DATE END_DATE PLAN_GROUP_ID PLAN_ID PCP_ID ---------- ---------- ---------- ------------- ------- ---------- U110 2004-06-01 2015-02-02 125 5 16297 U110 2015-02-03 2015-03-22 125 5 24557 U110 2015-03-23 2015-09-30 125 9 24557 U110 2015-10-01 2100-12-31 125 5 24557 Hope this helps, Oleg [1]: https://msdn.microsoft.com/en-us/library/hh231256.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Problem solved by Oleg. Thanks!
0 Likes 0 ·
@TheMac - If Oleg's answer solved your problem, please tick it as an Accepted answer.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.