Adjusting Employee Hours - SQL Server Forums

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)

Adjusting Employee Hours

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-09 : 10:36:33

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 11:34:49

try following these directions so we can actually help you.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-09 : 11:39:40

We've already had that dance

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50875

And before I put my time into to this one, I need to understand why you want to do this...because it makes no sense, and I'm sure your getting the requirement wrong.

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-09 : 12:07:42

Maybe I ddn't explain it properly. It's a telemarketing comapany. The business logic is unique I guess. They bill clients for callable hours which is the ITM and OTM projects. The TPV and Audit projects are not callable, but we still need to charge. So the business logic they came up with is to take the total uncallable hours and prorate the callable hours based on a percentage.

Ex:
Total callable: 25 hours
Total uncallbale: 11 hours

Emp 4 has 10 callable hours. Calculate the % by dividing the hours by the total callable (10/25 = .4) Multiply that by the total uncallable (.4 * 11 = 4.4) And that has to be added to the employee hours.

The earlier post just adjusted the uncallable hours evenly over the callable hours. This time the business team wants to do it with the formula.

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-09 : 12:09:11

And I need to use table 2 to figure out which project's hours need to be adjusted.

Thank you in advance for any help.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-09 : 13:39:48

I still don't think the second recordset tells you much...
but whatever:


SET NOCOUNT ON
CREATE TABLE #myTable (Proj varchar(5), Emp int, Task varchar(10), Hours decimal(15,2))

INSERT INTO #myTable(Proj, Emp, Task, Hours) 
SELECT 'A1', 1, 'TPV', 5 UNION ALL 
SELECT 'A1', 2, 'TPV', 2 UNION ALL 
SELECT 'A3', 3, 'AUDIT', 4  UNION ALL
SELECT 'A2', 4, 'ITM', 10 UNION ALL
SELECT 'A2', 5, 'ITM', 15

Select
	Proj,
	Emp,
	Task,
	Hours,
	AdjustedHrs = ((hours/callable) * uncallable) + hours
From #myTable A, 
(Select callable = sum(hours) From #myTable Where Task in ('ITM','OTM')) B, 
(Select uncallable = sum(hours) From #myTable Where Task in ('TPV','AUDIT')) C
Where A.Task in ('ITM','OTM')

Select 
	A.Proj, 
	A.Task, 
	AdjProject = case when A.task not in ('ITM','OTM') then B.Proj else null end, 
	Billable = case when A.task not in ('ITM','OTM') then 1 else 0 end,
	PercentBillable = case when A.task not in ('ITM','OTM') then 100 else 0 end
From #myTable A, 
(Select proj, task From #myTable Where Task in ('ITM','OTM')) B
Group By A.proj, A.task, B.proj

Drop Table #myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

ninel
Posting Yak Master

141 Posts

Posted - 2005-06-09 : 13:52:30

Seventhnight, thank you for responce. It was helpful, but I cannot hardcode the ITM/OTM, TPV/AUDIT which is why I need to use the second table:

Project Task AdjProject Billable PercentBillable
A1 TPV A2 1 100%
A2 ITM 0 0
A3 AUDIT A2 1 100%

This table shows that billable = 1 for noncallable hours that need to be applied to adjProject A2.

I need to figure out how to use this table in the query.

Thanks,
Ninel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources