Calculating the adjustment of employee hours

I work for a telemarketing company. I have a table that looks like
this:
Project Emp Task Hours
A 1 sales 10
A 2 sales 15
A 3 sales 5
A 4 sales 20
A 5 QA 10
A 6 Audit 5
For project A, I need the total hours for tasks QA and Audit. In this
case it would be 15 hours. Then I need to take that 15 hours and
evenly distribute them among the rest of the records for project A
and update the adjHours for QA and Audit with 0s.
The results should look like this:
Project Emp Task Hours AdjHours
A 1 sales 10 13.75
A 2 sales 15 18.75
A 3 sales 5 8.75
A 4 sales 20 23.75
A 5 QA 10 0
A 6 Audit 5 0
This needs to be an automated process. Can anyone help?
Thanks,
NinelHi
Maybe
SELECT Project, Emp, Task,
CASE WHEN Task in ('Audit','QA') THEN 0 ELSE HOURS + ( SELECT CAST(SUM(CASE
WHEN Task in ('Audit','QA') THEN Hours ELSE 0 END) AS DECIMAL(8,3))/SUM(CASE
WHEN Task in ('Audit','QA') THEN 0 ELSE 1 END)
FROM #ProjectTime ) END AS Hours
FROM #ProjectTime
You would need to be careful of divide by zero errors and you may have to
group the sumations and/ or it change to use a derived table.
John
"ninel" wrote:

> I work for a telemarketing company. I have a table that looks like
> this:
> Project Emp Task Hours
> A 1 sales 10
> A 2 sales 15
> A 3 sales 5
> A 4 sales 20
> A 5 QA 10
> A 6 Audit 5
> For project A, I need the total hours for tasks QA and Audit. In this
> case it would be 15 hours. Then I need to take that 15 hours and
> evenly distribute them among the rest of the records for project A
> and update the adjHours for QA and Audit with 0s.
> The results should look like this:
> Project Emp Task Hours AdjHours
> A 1 sales 10 13.75
> A 2 sales 15 18.75
> A 3 sales 5 8.75
> A 4 sales 20 23.75
> A 5 QA 10 0
> A 6 Audit 5 0
> This needs to be an automated process. Can anyone help?
> Thanks,
> Ninel
>|||http://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"ninel" wrote:

> I work for a telemarketing company. I have a table that looks like
> this:
> Project Emp Task Hours
> A 1 sales 10
> A 2 sales 15
> A 3 sales 5
> A 4 sales 20
> A 5 QA 10
> A 6 Audit 5
> For project A, I need the total hours for tasks QA and Audit. In this
> case it would be 15 hours. Then I need to take that 15 hours and
> evenly distribute them among the rest of the records for project A
> and update the adjHours for QA and Audit with 0s.
> The results should look like this:
> Project Emp Task Hours AdjHours
> A 1 sales 10 13.75
> A 2 sales 15 18.75
> A 3 sales 5 8.75
> A 4 sales 20 23.75
> A 5 QA 10 0
> A 6 Audit 5 0
> This needs to be an automated process. Can anyone help?
> Thanks,
> Ninel
>