Adjusting Employee Hours

I wrote a post earlier, but my manager has asked that I do it
differently. I have a table with employee hours for particular
projects. (I work for a telemarketing company)
Table1:
Project Task Emp Hours
A1 TPV 1 5
A1 TPV 2 2
A3 AUDIT 3 4
TOTAL: 11
A2 ITM 4 10
A2 ITM 5 15
TOTAL: 25
I need to figure out the adjusted hours for these employees. The end
result should look like this:
Project Task Emp Hours AdjHours
A1 TPV 1 5 0
A1 TPV 2 2 0
A3 AUDIT 3 4 0
A2 ITM 4 10 14.4
A2 ITM 5 15 21.6
I need to add the hours for the TPV and Audit records and adjust
against the ITM records by doing the following calculation:
Emp1, 2, 3 hours (tpv and audit) = 11 hours
Emp4, 5 hours = 25 hours
Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
(project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
14.4
Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
(project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
21.6
I cannot hardcode anything. I need to use the table2 to figure out the
projects I am totaling and the projects to adjust against.
Table2:
Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%
Billable = 1 tells me that projects A1 and A3 need to be adjusted
against project A2.
Please, can anyone help?
Thanks,
NinelHi
The principles are the same as your previous post.
SELECT p.Project, p.Emp, p.Task, p.Hours,
CASE WHEN p.Task in ('Audit','TPV') THEN 0 ELSE p.hours + (p.Hours/t.total1)
* t.total2 END
FROM #ProjectTime P,
( SELECT CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN 0 ELSE Hours END)
AS DECIMAL(8,3)) as Total1,
CAST(SUM(CASE WHEN Task in ('Audit','TPV') THEN Hours ELSE 0 END) AS
DECIMAL(8,3)) as Total2
FROM #ProjectTime ) t
John
"ninel" wrote:

> I wrote a post earlier, but my manager has asked that I do it
> differently. I have a table with employee hours for particular
> projects. (I work for a telemarketing company)
> Table1:
> Project Task Emp Hours
> A1 TPV 1 5
> A1 TPV 2 2
> A3 AUDIT 3 4
> TOTAL: 11
> A2 ITM 4 10
> A2 ITM 5 15
> TOTAL: 25
> I need to figure out the adjusted hours for these employees. The end
> result should look like this:
> Project Task Emp Hours AdjHours
> A1 TPV 1 5 0
> A1 TPV 2 2 0
> A3 AUDIT 3 4 0
> A2 ITM 4 10 14.4
> A2 ITM 5 15 21.6
> I need to add the hours for the TPV and Audit records and adjust
> against the ITM records by doing the following calculation:
> Emp1, 2, 3 hours (tpv and audit) = 11 hours
> Emp4, 5 hours = 25 hours
> Employee 4: 10 (hours) / 25(project A2) = .4 * 11 (total TPV+Audit
> (project A1+A3)) = 4.4 + 10 (emp4 hours). Adjusted hours for emp4 =
> 14.4
> Employee 5: 15 (hours) / 25(project A2) = .6 * 11 (total TPV+Audit
> (project A1+A3)) = 6.6 + 15 (emp5 hours). Adjusted hours for emp5 =
> 21.6
> I cannot hardcode anything. I need to use the table2 to figure out the
> projects I am totaling and the projects to adjust against.
> Table2:
> Project Task AdjProject Billable PercentBillable
> A1 TPV A2 1 100%
> A2 ITM 0 0
> A3 AUDIT A2 1 100%
> Billable = 1 tells me that projects A1 and A3 need to be adjusted
> against project A2.
> Please, can anyone help?
> Thanks,
> Ninel
>