Thread: Help with a Query
10-22-04, 18:53 #1Registered User
- Join Date
- Jun 2002
Unanswered: 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?
Name, ResourceID(This field is the FK relation ship to the other two tables.)
ResourceID,HourlyBillRate,EffectiveDate(The date the rate takes affect.)
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.name
10-22-04, 20:01 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
That'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."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."