Effective Date Query

[pinner]

I'm having some trouble with filtering data within multiple date ranges.

I have a number of projects, approximately 150, which pay our employees. Each project has a specific date range, from a couple months to a year. We also have projects that do not have an end date. Here's an example:

Project  Start_Date  End_Date
00123    09/01/2006  08/31/2007
22234    01/01/2007  12/31/2007
32222    03/01/2007  02/28/2008
55555    05/01/2005  12/31/2099 (no end date)
74585    09/29/2006  09/28/2007
93840    07/01/2006  06/30/2007

We use a set of rates for cost calculations. There are three types of rates which change over time.

Effective_Date  Rate_Type   Rate
9/1/2005             UI     0.30
9/1/2005             PT     0.16
9/1/2005             FT     0.35
9/1/2006             UI     0.31
9/1/2006             FT     0.36
3/1/2007             PT     0.18

Based on the project's date range, I need to use the appropriate rate.

Here are the caveats:
- a project's budget is based on the rates that were in effect at the time the budget was submitted, which is at the start of the project. A project must use the rate that it was created with for the duration of the project. So if a new rate becomes effective midway through a project year, the project cannot use the most current rate - it must use the "old" rate.
- projects with an open end date use rates with the most recent effective date.

For example, merging the data above and inserting an employee id, here's a example of the end result:

EmpID  Project  Start_Date  End_Date    Rate_Type   Rate
RDALE  00123    09/01/2006  08/31/2007   FT         0.36
JDOE   22234    01/01/2007  12/31/2007   UI         0.31
FSMIT  32222    03/01/2007  02/28/2008   PT         0.18
HCLIN  00123    09/01/2006  08/31/2006   PT         0.16
WPAYT  55555    05/01/2005  12/31/2099   FT         0.36
HPOTT  93840    07/01/2006  06/30/2007   UI         0.30
CBROW  93840    07/01/2006  06/30/2007   FT         0.35

I apologize for the lengthy post. I hope I wasn't too confusing.

I've tried the max suggestions from other posts without any success. All suggestions are greatly appreciated.

Thanks in advance.
Cheers.