SQL - Date Ranges

[mbabbitt]

I have a table with a field containing multiple lookup keys each representing unique health care providers. They have named plans (a field) that have a start_date (a field)and an end_date (a field that can contain a NULL if no end_date yet). Plans end (due to contract conditions) and can be restarted. So you can end up with multiple start_date(s) and multiple end_date(s).
How can I select out the range of a particular plan for a particular provider (per lookup_key): the minumum/earliest start_date and the maximum/latest end_date -- and sometimes that end date will be NULL?
However, if the next start_date is more than one day difference (when start_date > end_date +1) no range is taken -- they are treated as separate plans.

I have tried min/max functions but have not been successful so far.

Thanks in advance for your help.

[YuckFou]

Is this what you'd like to do ?

select Lookup_key , Plan_name , min(Start_date) as StartD , max(isnull(End_date,'99991231')) as EndD
from YourTable
group by Lookup_key , Plan_name

[mbabbitt]

Yes, Thanks Yuckyou; that begins the query but the query must differentiate the following: if the next start_date is 1 day after an end_date that is 1 range and so the earliest start and end dates are taken (#1 below), but if more than 1 day apart (#2 below), that starts a new range (as if it is a whole new plan) with the new start_date and its new end_date, which will probably be null if the plan is continued.

1) start...........end.start...noEnd(Null)

2) start....end......start....noEnd(Null)

#2 should return 2 records for the plan and lookup key but # 1 should only return 1 record.

Thanks in advance.