Combine two overlapping date ranges into one

Database
Fan

Combine two overlapping date ranges into one

I have 2 coverage spans for each member, Member_coverage and Other_insurance_coverage and I need to split the member coverage span into multiple spans based on the other insurance. There might be gaps in between the other_insurance_coverage like in example 3.







Key Member_coverage other_insurance_coverage Expected Result
Mbr1Enrl1 ('2005-01-01', '2005-06-30') ('2005-09-01', '2006-10-31') ('2005-01-01', '2005-06-30')
    ('2007-04-01', '2008-01-31')  
    ('2008-06-01', '2009-01-31')  
    ('2009-06-01', '2009-10-31')  
Mbr1Enrl2 ('2005-07-01', '2006-06-30') ('2005-09-01', '2006-10-31') ('2005-07-01', '2005-08-31')
    ('2007-04-01', '2008-01-31') ('2005-09-01', '2006-06-30')
    ('2008-06-01', '2009-01-31')  
    ('2009-06-01', '2009-10-31')  
Mbr2Enrl1 ('2006-07-01', '2007-06-30') ('2005-09-01', '2006-10-31') ('2006-07-01', '2006-10-31')
    ('2007-04-01', '2008-01-31') ('2006-11-01', '2007-03-31')
    ('2008-06-01', '2009-01-31') ('2007-04-01', '2007-06-30')
    ('2009-06-01', '2009-10-31')  
Mbr2Enrl2 ('2007-07-01', '2008-06-30') ('2005-09-01', '2006-10-31') ('2007-07-01', '2008-01-31')
    ('2007-04-01', '2008-01-31') ('2008-02-01', '2008-05-31')
    ('2008-06-01', '2009-01-31') ('2008-06-01', '2008-06-30')
    ('2009-06-01', '2009-10-31')  

I guess others have encountered a similar scenario. Any suggestions?

Thanks in advance for your help!

-gk

2 REPLIES
Fan

Re: Combine two overlapping date ranges into one

Uploaded screenshot of the examples for easier understanding.

Fan

Re: Combine two overlapping date ranges into one

Dieter, Can you suggest something on this please?