September 5, 2005 at 8:07 pm
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
September 5, 2005 at 8:20 pm
Hi, there is something that I am not clear. In your Rates table, do you store data like this?
projID empID rate
A 100
A 001 50
B 002 150
-------------------------
Cost for project A = (50 * hour) and project B = (150)?
or, cost for project A = (150 * hour)?
We may use different query to calculate it.
thanks.
Leo
September 5, 2005 at 8:25 pm
The table structure is fine, but can you supply some sample data, and what your expected output is?
Seems to me you could perform a union. Calculate the default rate hours separately from the EmployeeRate hours.
select p.projID, p.projName, sum(Cost) AS totalCost
from (SELECT p.projID, p.projName, r.rate * ph.hours as Cost
FROM Projects p, ProjectHours ph, Rates r
WHERE p.projID=r.projID AND empID IS NULL
GROUP BY p.projID, p.projName
Union All
SELECT p.projID, p.projName, r.rate * ph.hours AS EmpCost
FROM Projects p, ProjectHours ph, Rates r
WHERE p.projID=r.projID AND empID IS NOT NULL
GROUP BY p.projID, p.projName) as DerivedTable
September 6, 2005 at 9:31 am
I didn't create your tables, so this is absolutely untested... but I think something similar should work, if I correctly understood your question and description of data. If there are problems, you can troubleshoot the inner part (derived table) separately, without summing the values, so that you can better see what's wrong. That's why there are so many columns in the derived table - to make testing easier. Once it works, you don't need half of them and can simplify the SQL.
SELECT Q.projID, Q.projName, SUM(Q.Cost) as TotalCost
FROM
(
SELECT p.projID, p.projName, ph.empID as employee, ph.hours, COALESCE(remp.rate, rdef.rate) AS rate,
ph.hours * COALESCE(remp.rate, rdef.rate) AS Cost
FROM Projects p
JOIN ProjectHours ph ON p.projID = ph.projID
LEFT JOIN Rates rdef ON p.projID = rdef.projID AND rdef.empID IS NULL /*default rate*/
LEFT JOIN Rates remp ON p.projID = remp.projID AND remp.empID = ph.empID /*rate for a specific empID*/
) as Q
GROUP BY Q.projID, Q.projName
HTH, Vladan (P.S. I'm on a vacation from tomorrow, if there is some error in my SQL, I'm sure others will help to put it straight)
September 6, 2005 at 5:04 pm
Thanks, everyone, for your help / suggestions. I'll give them a try later tonight / tomorrow.
FYI, here's some sample data:
Employees
empID empName
----------------------
10 Josh
11 Bob
Projects
projID projName
----------------------
20 Project A
21 Another Project
ProjectHours
empID projID hours
----------------------
10 20 2
10 21 3
11 20 5
11 21 4
Rates
projID empID rate
----------------------
20 30
21 40
21 11 50
The report should look something like:
Project A 210
Another Project 320
And a more detailed report by expanding the employees (I would assume simply by changing the GROUP BY). Notice that Bob has a specific rate set on projID=21 and thus will use his rate and not the projects default rate. This report looks like:
Project A
Josh 60 (2*30)
Bob 150 (5*30)
Another Project
Josh 120 (3*40)
Bob 200 (4*50)
September 7, 2005 at 5:27 am
Vacation begins in the afternoon, so I can make one more post 🙂 Well, it seems that I understood correctly, at least on this small sample my query returns required results.
create table projects (projID int, projName varchar(40))
insert into projects (projID, projName) values (20, 'Project A')
insert into projects (projID, projName) values (21, 'Another Project')
Create table employees (empID int, empName varchar(40))
insert into employees (empID, empName) values (10,'Josh')
insert into employees (empID, empName) values (11,'Bob')
create table projectHours (empID int, projID int, hours int)
insert into projectHours (empID, projID, hours) values (10,20,2)
insert into projectHours (empID, projID, hours) values (10,21,3)
insert into projectHours (empID, projID, hours) values (11,20,5)
insert into projectHours (empID, projID, hours) values (11,21,4)
create table rates (projID int, empID int, rate int)
insert into rates (projID, empID, rate) values (20,NULL,30)
insert into rates (projID, empID, rate) values (21,NULL,40)
insert into rates (projID, empID, rate) values (21,11,50)
/*project sums*/
SELECT Q.projID, Q.projName, SUM(Q.Cost) as TotalCost
FROM
(
SELECT p.projID, p.projName, ph.empID as employee, ph.hours * COALESCE(remp.rate, rdef.rate) AS Cost
FROM Projects p
JOIN ProjectHours ph ON p.projID = ph.projID
LEFT JOIN Rates rdef ON p.projID = rdef.projID AND rdef.empID IS NULL /*default rate*/
LEFT JOIN Rates remp ON p.projID = remp.projID AND remp.empID = ph.empID /*rate for a specific empID*/
) as Q
GROUP BY Q.projID, Q.projName
ORDER BY Q.projID
/*project data by employee*/
SELECT p.projID, p.projName, ph.empID as employee, e.empName, ph.hours * COALESCE(remp.rate, rdef.rate) AS Cost,
ph.hours, COALESCE(remp.rate, rdef.rate) AS rate
FROM Projects p
JOIN ProjectHours ph ON p.projID = ph.projID
JOIN employees e ON e.empID = ph.empID
LEFT JOIN Rates rdef ON p.projID = rdef.projID AND rdef.empID IS NULL /*default rate*/
LEFT JOIN Rates remp ON p.projID = remp.projID AND remp.empID = ph.empID /*rate for a specific empID*/
ORDER BY by p.projID, ph.empID
The rest is a matter of formatting the output, which should probably be done on the client side, not on the server. If necessary, it is possible to write SQL that formats the data, but I always try to avoid it.
HTH, Vladan
September 7, 2005 at 9:50 pm
OK. Got it working with your suggestions. 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.
September 14, 2005 at 8:14 am
Hello,
I'm back from vacation... and it looks that there still is one question pending. OK... I'd strongly suggest to use both startDate and endDate. This should allow you to use the same query with a small modification.
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 (r1.projID = ph.projID and r1.empID=ph.empID and ph.date BETWEEN r1.startDate and r1.endDate)
LEFT JOIN Rates r2 ON (ph.projID=r2.projID and r2.empID IS NULL and ph.date BETWEEN r2.startDate and r2.endDate)
WHERE p.projID=ph.projID
GROUP BY p.projID,p.projName
If you only use one column - startDate, you'll need derived tables and/or something like "date > startDate and date < (select min(startDate) from rates where startDate > ....." instead of a simple join on BETWEEN. It should be possible to create a trigger that sets the EndDate of previous rate to a correct value when a new rate is entered... however, I'm not very experienced in this area, you should better ask someone else about triggers, if you want to use them :-).
Cheers, Vladan
EDIT : Just realized that the dates can be NULL... in that case, use BETWEEN COALESCE(r.startDate,'19600101') AND COALESCE (r.endDate, '49991231') to get the required result... replacing "r." with "r1." and "r2.", respectively.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply