Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

jman
Starting Member

2 Posts

Posted - 2005-09-05 : 21:15:12

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

Kristen
Test

22859 Posts

Posted - 2005-09-06 : 02:24:12

Hi jman, Welcome to SQL Team!

I reckon you were missing a JOIN on the ProjectHours table - that's easy to miss with the old style syntax you were using, easier to not miss using JOIN syntax! - which will give you a cartesian join.

I figure to just COALESCE the Employee rate (which may be NULL) with the Default rate:


SELECT p.projID, 
       p.projName, 
       sum(COALESCE(R_Emp.rate, R_Default.rate) * ph.hours) AS totalCost
  FROM Projects p
       JOIN ProjectHours ph 
           ON ph.projID = p.projID
       JOIN Rates R_Default
           ON R_Default.projID = p.projID
          AND R_Default.empID IS NULL
       LEFT OUTER JOIN Rates R_Emp
           ON R_Emp.projID = p.projID
          AND R_Emp.empID = ph.empID
GROUP BY p.projID, p.projName

Kristen

jman
Starting Member

2 Posts

Posted - 2005-09-07 : 23:38:55

OK. Got it working with your suggestion. However, I've got one more twist... if you don't mind. This should cover all my bases on this query.

An employee's rate might change at some point. Changing their rate is easy, however, the new rate should be reflected on a given date and beyond (not affecting the rates on previous dates).

I've altered the Rates table and added an additional column: startDate. An endDate column might be required to simplify the query... but if it's not needed in the query, I shouldn't need it for anything else. I attempted to get this to work but I'm getting double the hours than I should (somethings not joining correctly -- getting duplicates).

Here's the query I used to get the original idea working:

SELECT p.projID,p.projName,
       SUM(COALESCE(r1.rate,r2.rate) * ph.hours) as totalCost
  FROM Projects p, ProjectHours ph
         LEFT JOIN Rates r1 ON (p.projID=r1.projID and r1.empID=ph.empID)
         LEFT JOIN Rates r2 ON (p.projID=r2.projID and r2.empID IS NULL)
 WHERE p.projID=ph.projID
GROUP BY p.projID,p.projName

Below is some sample data with minor adjustments to the tables. When Bob puts hours down for "Another Project" he will be charging at a rate of 50 (for hours before 9/6/05). Any hours after 9/6/05 will be at a new rate of 100. Then starting 1/1/06 his rate bumps up to 150. The query above uses the Rates table correctly but is not comparing the ph.date to the r1.startDate to grab the correct rate (based on the date the hours were entered for) - I couldn't get this working properly. So a report for Bob on "Another Project" will figure the hours/rates like the following:

[04-SEP-05] 4 * 50 = 200
[07-SEP-05] 8 * 100 = 800
[03-FEB-06] 2 * 150 = 300

Sample Data:

Employees
empID     empName
----------------------
10        Josh
11        Bob

Projects
projID    projName
----------------------
20        Project A
21        Another Project


ProjectHours
empID    projID    hours    date
---------------------------------------
10       20        2        01-SEP-05
10       21        3        01-SEP-05
11       20        5        02-SEP-05
11       21        4        04-SEP-05
11       21        8        07-SEP-05
11       21        2        03-FEB-06

Rates
projID   empID    rate    startDate
-----------------------------------
20                30
21                40
21       11       50
21       11       100     06-SEP-05
21       11       150     01-JAN-06

** Rates might need an endDate to get this to work properly?? In that case the endDate on an old rate would be the day before the startDate of the new rate. If the startDate or endDate is NULL it should mean "infinite" in that direction.

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 06:22:58

You'll need to work on a sub-query that will find an Employee Rate that was valid at the time

I do have one issue with this: Rates.empID can be NULL, and therefore can't be part of your PK - and therefore there is a risk that you will have duplicate values. Ditto with the new Rates.startDate


SELECT p.projID, 
       p.projName, 
       sum(COALESCE(R_Emp.rate, R_Default.rate) * ph.hours) AS totalCost
  FROM Projects AS p
       JOIN ProjectHours AS ph 
           ON ph.projID = p.projID
       JOIN Rates AS R_Default
           ON R_Default.projID = P.projID
          AND R_Default.empID IS NULL
          AND R_Default.startDate IS NULL  -- Belt&Braces! and future-proofing
       LEFT OUTER JOIN Rates AS R_Emp
           ON R_Emp.projID = p.projID
          AND R_Emp.empID = ph.empID
          AND R_Emp.startDate >= PH.[date]  -- Start date is valid
          AND NOT EXISTS
          (
              -- Only if there is no later Employee rate with a more suitable start date
              SELECT *
              FROM   Rates AS R_Emp2
              WHERE      R_Emp2.startDate > R_Emp.startDate 
                     AND R_Emp2.startDate <= PH.[date]
          )
GROUP BY p.projID, p.projName

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources