Collapse / aggregate date ranges

Database
Enthusiast

Collapse / aggregate date ranges

Hi,

I need some logic help. I think I read a post about a year ago for a travel database where the person wanted to find the first departure time and final arrival time for flights with multiple stops. However, I can't get what I remember to work and of course I can't find the post through Google or this forum's search. My problem is enrollment data, for example:

Member Product Enr_Start Enr_End RX_Start RX_End
0001 0111 1/1/2005 12/31/2100 1/1/2005 12/31/2006
0001 0111 1/1/2005 12/31/2100 1/1/2007 12/31/2007
0001 0111 1/1/2005 12/31/2100 1/1/2008 4/15/2008
0001 0222 4/16/2008 12/31/2100 5/1/2008 12/31/2008
0001 0111 1/1/2005 12/31/2100 1/1/2009 7/1/2010
...

I need the ouput to be:

Member Product Enr_Start Enr_End RX_Flag
0001 0111 1/1/2005 4/15/2008 Y
0001 0111 4/16/2008 12/31/2008 N
0001 0111 1/1/2009 7/1/2010 Y
0001 0222 4/16/2008 4/30/2008 N
0001 0222 5/1/2008 12/31/2008 Y
0001 0222 1/1/2009 12/31/2100 N

I am imagining a person has medical and prescription coverage through work until April 2008. Then they get married and keep their general coverage but switch prescription to their spouse's plan for the remainder of 2008. Then back to their original plan in 2009.

I think my problem is that there are multiple rows with the same results that just change the start and end dates, like I showed from 2005 to 2008.

I don't have permissions for UDF and I don't know much about dynamic SQL. But based on the travel data I think I remember this can be solved with normal SQL. All I can think of is procedural steps, like take the first enrollment date and step through a member/product/enrollment checking for gaps in dates or change in status. If found end date it when the gap or change is found. Then start a new row with the next date until another gap or change is found. I can't just use min and max partitioned by the member, product and enrollment because that would ignore gaps. I need a flag or indicator that I can't figure out.

I have pretty much wasted a week trying different combinations or partitions and ordering, but I am still missing something. A lot of very smart people read this forum, so if someone has an idea how to solve the problem please let me know.

Thank you.
1 REPLY
Enthusiast

Re: Collapse / aggregate date ranges

Ok, I have to apologize.

I posted my question then I found the solution... thanks for letting me vocalize the problem though.

I will give credit where credit is due; in the SQL Cookbook from O'Reilly the author Anthony Molinaro gives a solution in section 10.3.

My problem was an indicator marking all the groups... His solution is to flag each new group with a 1 and then sum the flags; so the first group is marked with a 1, the second group a 2 and so on. Then I can get the min/max for each group.

If you have another solution I would still love to read it.

Thanks for the help and again I apologize if I wasted your time.