Calculation for adjusting employee hours

I work for a telemarketing company. I have a table that looks like this:
Proj 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:
Proj Empl 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
Message posted via http://www.webservertalk.comTry,
update t1
set adjhours = hours + (
select isnull(sum(case when a.task = 'QA' or a.task = 'Audit' then a.hours
else 0 end) / nullif(sum(case when a.task = 'QA' or a.task = 'Audit' then 0
else 1 end), 0), 0)
from t1 as a
where a.proj = t1.proj
)
where
proj = 'A'
and task not in ('QA', 'Audit')
AMB
"ninel gorbunov via webservertalk.com" wrote:

> I work for a telemarketing company. I have a table that looks like this:
> Proj 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:
> Proj Empl 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
> --
> Message posted via http://www.webservertalk.com
>|||Have you considered doing this as a query rather than an UPDATE? That way
you preserve all the information but you can still display the required
values:
SELECT proj, emp, task,
CASE WHEN task IN ('QA','Audit') THEN 0 ELSE hours+
(SELECT SUM(CASE WHEN task IN ('QA','Audit') THEN hours END)
/CAST(SUM(CASE WHEN task NOT IN ('QA','Audit') THEN 1 END) AS REAL)
FROM TaskHours
WHERE proj = T.proj) END
FROM TaskHours AS T
Otherwise, just put the subquery into an UPDATE statement.
David Portas
SQL Server MVP
--|||Untested:
update tableName set hours =
case task
when 'QA' then 0
when 'Audit' then 0
else Hours + (select sum(hours)/count(hours) from tableName where task =
'sales' and Proj = 'A')
END
where Proj = 'A'
"ninel gorbunov via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in
message news:dcb239fc53fe40a98c155bc2a26b3fd0@.SQ
webservertalk.com...
>I work for a telemarketing company. I have a table that looks like this:
> Proj 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:
> Proj Empl 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
> --
> Message posted via http://www.webservertalk.com|||You could write an AFTER INSERT, UPDATE, DELETE trigger to recalculate the
Hours for each project.
CREATE TRIGGER triggerName ON tableName AFTER INSERT, UPDATE, DELETE AS
BEGIN
UPDATE tableName
SET adjHours =
CASE
WHEN Task = 'sales' THEN Hours + b.distHours
ELSE 0.0
END
FROM
(SELECT Proj,
SUM(CASE
WHEN Task = 'sales' THEN 0.0
ELSE Hours
END) / CASE
WHEN SUM(CASE
WHEN Task = 'sales'
THEN 1
ELSE 0
END) = 0 THEN 1.0
ELSE SUM(CASE
WHEN Task = 'sales'
THEN 1.0
ELSE 0.0
END)
END AS distHours
FROM tableName
WHERE Proj IN (SELECT DISTINCT Proj FROM deleted
UNION SELECT DISTINCT Proj FROM
inserted)) b
WHERE tableName.Proj = b.Proj
END
I didn't test this, but it should point you in the right direction.
The extra case is necessary to prevent division by zero. I assume it is
possible for a QA or Audit record to be inserted before any sales records,
but since it forces adjHours to zero in that case, there isn't any need to
worry about the 1.0 in the denominator at that point. If there are any sale
s
records, the denominator will reflect the correct number of sales records.
If there aren't any, distHours will be ignored, so it doesn't matter what th
e
denominator is as long as it's not zero.
"ninel gorbunov via webservertalk.com" wrote:

> I work for a telemarketing company. I have a table that looks like this:
> Proj 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:
> Proj Empl 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
> --
> Message posted via http://www.webservertalk.com
>|||OOPS! I forgot the group by
CREATE TRIGGER triggerName ON tableName AFTER INSERT, UPDATE, DELETE AS
BEGIN
UPDATE tableName
SET adjHours =
CASE
WHEN Task = 'sales' THEN Hours + b.distHours
ELSE 0.0
END
FROM
(SELECT Proj,
SUM(CASE
WHEN Task = 'sales' THEN 0.0
ELSE Hours
END) / CASE
WHEN SUM(CASE
WHEN Task =
'sales' THEN 1
ELSE 0
END) = 0 THEN 1.0
ELSE SUM(CASE
WHEN Task = 'sales'
THEN 1.0
ELSE 0.0
END)
END AS distHours
FROM tableName
WHERE Proj IN (SELECT DISTINCT Proj FROM deleted
UNION SELECT DISTINCT Proj FROM inserted)
GROUP BY Proj) b
WHERE tableName.Proj = b.Proj
END
> I didn't test this, but it should point you in the right direction.
> The extra case is necessary to prevent division by zero. I assume it is
> possible for a QA or Audit record to be inserted before any sales records,
> but since it forces adjHours to zero in that case, there isn't any need to
> worry about the 1.0 in the denominator at that point. If there are any sa
les
> records, the denominator will reflect the correct number of sales records.
> If there aren't any, distHours will be ignored, so it doesn't matter what
the
> denominator is as long as it's not zero.
> "ninel gorbunov via webservertalk.com" wrote:
>|||This was great. Thank you, but now my manager just changed things around
for me.
Table1:
Proj 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
Now he wants to calculate the percentage of each employee over the total
hours.
Emp1: 10/50 = 20%
Emp2: 15/50 = 30%
Emp3: 5/50 = 10%
Emp4: 20/50 = 40%
And then calculate what the adjusted hours should be:
Emp1: 20% * 15(Total QA and Audit) = 13
Emp2: 30% * 15(Total QA and Audit) = 19.5
Emp3: 10% * 15(Total QA and Audit) = 6.5
Emp4: 40% * 15(Total QA and Audit) = 26
Result Table:
Proj Empl Task Hours AdjHours
A 1 sales 10 13
A 2 sales 15 19.5
A 3 sales 5 6.5
A 4 sales 20 26
A 5 QA 10 0
A 6 Audit 5 0
Message posted via http://www.webservertalk.com