Returning Value Located Between Two Date Ranges

I have 2 tables where I need to pull data from one table into the other.

Table 1 - pull data here
A
Site
B
Begin Date
C
End Date
Rate

[Code]...

For example: site 77 in table 1, I need to pull whatever rate is shown for site 77 in table 2 for the date range specified on table 1. So the correct answer for the first row site 77 in column E would be .02. Notice there is a problem...mainly the dates do not match exactly. I just need the rate the applies for the majority of the time.

Another example. For table 1, site 174 from 2/11/11 to 2/22/12, the correct answer in column E (pulled from table 2) would be .02, even though Column D shows .00.