I have a table with the following information in it: Hospital Name, Type of Visit, Start Date, End Date, and Rate.

There are 8 different Hospitals, 3 different Visit Types, varying Start/End dates for each Hospital/Visit Type, and then of course a specific rates corresponding to the previous criteria.

So for example, let's say

NYU Medical Center, ER Visit, 12/1/08 to 6/30/09 = $300

The problem is the date ranges are different for each. So, in the example above, NYU Medical Center might have 11 different date ranges to pick from to get the correct rate for ER service. But they might only have 5 different date ranges to pick from to get the correct rate for Surgery Services.

I'm trying to figure out if there is a way for me to just enter the Hospital Name, Type of Service, and a Date of Service, and basically go "If Hospital name = x, Type of service = y, and Date Rage is between 'a' and 'b' then rate = z." -- if they were all uniform date ranges, this would be much easier it seems. But because I'm not only dealing with different amounts of date ranges, but also different actual date ranges themselves, this seems much more tricky.

Just to provide a further example, one set of date ranges for one hospital and visit type is:

12/1/2008 6/30/2009
7/1/2009 11/30/2009
12/1/2009 12/31/2009
1/1/2010 6/30/2010
7/1/2010 12/31/9999 (present)

And for the SAME hospital, but DIFFERENT Service Type, they are:

12/1/2008 12/31/2008
1/1/2009 6/30/2009
7/1/2009 11/30/2009
12/1/2009 12/31/2009
1/1/2010 6/30/2010
7/1/2010 3/31/2011
4/1/2011 6/30/2011
7/1/2011 12/31/9999 (present)

Thanks in advance as always.