Help with a Query

What I need to do is select a persons name and total up there hours worked times their bill rate. The trick is that they can have multiple bill rates based on the effective date of that bill rate. Their bill rate( there can be multiple bill rates per person), Effective date (There is one effective date per bill rate per person), Total hours hours worked, where work is broken up to the day the work was done. So I need to multiply the bill rates on the day the work was performed and the total them up.

Example--John works 200 hours in june at $100 and hour, then we change his bill rate to $150 on July first. He then works 100 hours in July. His total bill would be $35,000.

Here is basically how the tables are configured. Any Suggestions?

Tables

--Resources--
Columns
Name, ResourceID(This field is the FK relation ship to the other two tables.)

--ResourceRate--
Columns
ResourceID,HourlyBillRate,EffectiveDate(The date the rate takes affect.)

--Time--
Columns
ResourceID,TimeDate(The day the work was performed),RegularHours(The number of hours worked on that day)

Below is mty attempt.

select B.EffectiveDate,CAST(A.name as char(20))Name, sum(C.RegularHours) As Total_Regular_Hours, B.HourlyBillRate
from Resources A INNER JOIN ResourceRate B ON A.ResourceID = B.ResourceID
INNER JOIN Time C ON C.ResourceID = A.ResourceID
Group by A.name, B.HourlyBillRate,B.EffectiveDate,C.TimeDate
order by A.nameThat's why I prefer a 2-field representation of a period. And in your case for as long as the rate is current, DateEnd would stay NULL, once the rate changes, - DateEnd acquires the last day for the old rate, and a new record with DateEnd=NULL gets created. By doing so you can sum HourlyBillRate X RegularHours by NAME and EffectiveDate WHERE TimeDate between EffectiveDate and DateEnd.