Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    19

    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?

    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.name

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •