Calculation for adjusting employee hours

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)

Calculation for adjusting employee hours

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-08 : 13:30:14

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

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-08 : 13:47:03

quote:
Originally posted by ninel

I work for a telemarketing company.

Let's keep that a secret...


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Proj char(1), Emp int, Task char(10), Hours decimal(15,2), AdjHours decimal(15,2))
GO

INSERT INTO myTable99(Proj, Emp, Task, Hours) 
SELECT 'A', 1, 'sales', 10 UNION ALL 
SELECT 'A', 2, 'sales', 15 UNION ALL 
SELECT 'A', 3, 'sales', 5  UNION ALL
SELECT 'A', 4, 'sales', 20 UNION ALL
SELECT 'A', 5, 'QA',    10 UNION ALL
SELECT 'A', 6, 'Audit', 5
GO

UPDATE myTable99
  SET AdjHours = Hours + SUM_HOURS/COUNT_SALES
FROM (
	SELECT SUM(l.Hours) AS SUM_HOURS
	  FROM myTable99 l
	 WHERE Task IN ('QA','Audit')) a
CROSS JOIN (
	SELECT COUNT(l.Hours) AS COUNT_SALES
	  FROM myTable99 l
	 WHERE Task = 'sales') b
WHERE Task = 'sales'
GO

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-08 : 15:58:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources