×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

SQL Query Help

SQL Query Help

SQL Query Help

(OP)
I'll explain the problem I'm running into throughout below.  To keep things simple I'll be stripping the tables to the bare necessities for my explanation.

We have a project tracking system our employees are required to fill out each day. They log in, select the project they worked on, and enter the number of hours they worked on it.  Managers then run reports, for budgetary reasons, that show them the cost of each project.  I fetch the report in a simple query that goes through all the projects (Projects table), joins each project up with their rate (Rates table), and sums up the cost for each project.  The tricky part now is a manager may set a specific rate for an employee working on project X.  Therefore when the query runs it must use a different rate (from the Rates table) for the given project / employee.

The calculation I'm using in the query is a basic:  sum(RATE * HOURS)

A quick summary of the table relationships:
All projects have a "default" rate associated with them and thus will have 1 entry in Rates for each project.  Any additional entries in Rates for a particular project will be because an empID was given to change the rate for a particular employee for that project.

The query I'm currently using to fetch a list of projects and their "default" costs:

SELECT p.projID, p.projName, sum(r.rate * ph.hours) AS totalCost
  FROM Projects p, ProjectHours ph, Rates r
 WHERE p.projID=r.projID
GROUP BY p.projID, p.projName

Now I just need to throw in the twist of an specific employee having a different rate then the "default" rate for a particular project.  Any help would be greatly appreciated.  The creation of a View is acceptable... but I'd rather see if this is possible using some combination of JOINS.

Tables below...

Employees  (snipped)
----------------------
empID
empName


Projects  (snipped)
----------------------
projID
projName


ProjectHours (snipped)
----------------------
empID
projID
hours


Rates  (snipped)
----------------------
projID  (not null)
empID  (will be null for the "default" project rate)
rate

RE: SQL Query Help

SELECT p.projID, p.projName, sum(r.rate * ph.hours) AS totalCost
  FROM Projects p, ProjectHours ph, Rates r, Employees e
 WHERE p.projID=r.projID and p.projID=ph.projID and e.empID=ph.empID and e.empID=r.empID
GROUP BY p.projID, p.projName

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources