question

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