Point in right direction...Stuck.

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 2005 Forums

Transact-SQL (2005)

Point in right direction...Stuck.

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-09 : 13:05:05

I didn't know if someone might be able to point me down the right path. I have a payroll project that I'm working on. I have several things that I'm stuck on.

Employee JoeSmith might work at several different locations during a week period. His hours can be assigned to 3 different categories. Anything over 40 hours in any one week is considered Overtime (OT).
My problem is assigning the OT to the correct category on the correct day.

JoeSmith timesheet:

12/1/2008 JoeSmith new york 12.0 hrs (cat1)
12/2/2008 JoeSmith boston 12.0 hrs (cat2)
12/3/2008 JoeSmith boston 12.0 hrs (cat2)
12/4/2008 JoeSmith boston 12.0 hrs (cat1)
12/5/2008 JoeSmith los angeles 12.0 hrs (cat3)

result should look like this:

joesmith newyork 12.0 hrs cat1 regular time
joesmith boston 24.0 hrs cat2 regular time 4.0 hrs cat1 regular time 8.0 hrs cat1 OT
joesmith los angeles 12.0 hrs cat3 OT

so when hrs > 40 which location he is at the OT starts to accumulate. i am really lost on how to approach this from SQL. hopefully someone could shed some light on the best way to approach this. thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 13:12:48

[code]select EmpName,place,SUM(worktime),category,
CASE WHEN MAX(TotalHours) > 40 then OT ELSE '' END
FROM table t
CROSS APPLY (SELECT SUM(worktime) AS TotalHours
FROM table
WHERE EmpName=t.EmpName
AND Date <=t.Date)t1
GROUP BY EmpName,place,category
[/code]

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-09 : 17:00:55

This almost gives me correct info. Here is what I did with your query:

select
EmployeeName,CostCenter,Rate,SUM(Hours)Hours,PayCategory,
CASE WHEN MAX(TotalHours) <= 40 then 'REG' ELSE 'OT' END AS PayType

FROM MWSPayroll t
CROSS APPLY (SELECT SUM(Hours) AS TotalHours
FROM MWSPayroll
WHERE EmployeeName=t.EmployeeName
AND PayrollDate <=t.PayrollDate)t1

GROUP BY EmployeeName,CostCenter,Rate,PayCategory

And here is what this has given me when tested:

Terry Turner MER001 15.50 7.00 FLOOR OT
Terry Turner MER001 20.00 20.00 DERIC REG
Terry Turner MER077 20.00 19.00 DERIC REG
Terry Turner MER077 20.50 5.00 DERIC OT
Terry Turner MER886 15.50 10.00 SWAB OT

There should be a total of 40 hours REG time. The fourth column shows that there is only 39 hours REG time. Row1 should show 1.00 FLOOR REG, then on another line show 6.00 FLOOR OT.

Do you know why this is doing what it is? I've been trying to get it right, but have had no luck. It is very close though. Thanks for your efforts.

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-09 : 17:35:08

I just realized I made an error when describing what I needed. The payroll is two weeks long. Anything over 40 hours in a given week is OT. The query that VISAKH16 has provided is almost perfect when dealing with a weeks worth of payroll, but when I roll in two weeks worth, it doesn't. I wish I knew more about this syntax and how it is working to be able to fix what was given. Any help would be appreciated. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 02:31:42

quote:
Originally posted by osupratt

I just realized I made an error when describing what I needed. The payroll is two weeks long. Anything over 40 hours in a given week is OT. The query that VISAKH16 has provided is almost perfect when dealing with a weeks worth of payroll, but when I roll in two weeks worth, it doesn't. I wish I knew more about this syntax and how it is working to be able to fix what was given. Any help would be appreciated. Thanks.

then you need to include week field also in GROUP BY and also in subquery to which you take cross apply. then it will check for overtime based on week value.

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-10 : 09:50:31

got it. thanks. why (when looking at the post prior to my last) is the REG hours totaling 39 hours instead of 40?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:49:27

quote:
Originally posted by osupratt

got it. thanks. why (when looking at the post prior to my last) is the REG hours totaling 39 hours instead of 40?

did you get this discrepancy even after grouping by week?

osupratt
Posting Yak Master

238 Posts

Posted - 2008-12-10 : 12:34:09

Yes. Here is my query:

SELECT
PayrollWeek,MAX(PayrollDate)PayrollDate,EmployeeName,
CostCenter,Rate,MAX(Hours)Hours,PayCategory,
CASE WHEN MAX(TotalHours) <=40 then 'REG' ELSE 'OT' END AS PayType,
ROW_NUMBER()OVER (PARTITION BY Misc_Text_Field_2,EmployeeName ORDER BY MAX(PayrollDate)),
MAX(TotalHours)TotalHours
FROM
MWSPayroll t

CROSS APPLY (SELECT SUM(Hours) AS TotalHours
FROM MWSPayroll
WHERE EmployeeName=t.EmployeeName
AND PayrollDate <=t.PayrollDate AND PayrollWeek =t.PayrollWeek)t1

GROUP BY PayrollWeek,EmployeeName,CostCenter,Rate,PayCategory
ORDER BY PayrollDate

Here is my result set. Look at the highlighted rows. The first one the 7 hours should be broken out in two rows. 1 hour REG (to bring this employees REG total to 40) and then the remainder 6 hours OT. The same with the other highlighted row. The 20 hours should be broken out into two rows. 5 hours REG (to bring total to 40) and then 15 hours OT.

1 2008-12-06 00:00:00.000 Juan Chavez MER021 20.00 15.00 DERIC REG 1 15.00
1 2008-12-06 00:00:00.000 Mario Velasquez MER001 10.00 15.00 SHOP1 REG 1 15.00
1 2008-12-06 00:00:00.000 Terry Turner MER001 20.00 20.00 DERIC REG 1 20.00
1 2008-12-07 00:00:00.000 Terry Turner MER077 20.00 19.00 DERIC REG 2 39.00
1 2008-12-08 00:00:00.000 Terry Turner MER001 15.50 7.00 FLOOR OT 3 46.00
1 2008-12-09 00:00:00.000 Terry Turner MER886 15.50 10.00 SWAB OT 4 56.00
1 2008-12-10 00:00:00.000 Terry Turner MER077 20.50 5.00 DERIC OT 5 61.00
2 2008-12-13 00:00:00.000 Terry Turner MER077 20.00 12.00 OPRTR REG 1 12.00
2 2008-12-14 00:00:00.000 Terry Turner MER101 20.00 13.00 SWAB REG 2 25.00
2 2008-12-15 00:00:00.000 Terry Turner MER001 20.00 10.00 FLOOR REG 3 35.00
2 2008-12-16 00:00:00.000 Terry Turner MER001 20.00 20.00 DERIC OT 4 55.00
2 2008-12-17 00:00:00.000 Terry Turner MER001 20.00 10.00 SHOP OT 5 65.00

Thanks again for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 21:40:06

[code];With CTE (PayrollWeek, PayrollDate, EmployeeName, CostCenter, Hours, PayCategory, Seq, TotalHours) AS
(
SELECT
PayrollWeek,MAX(PayrollDate)PayrollDate,EmployeeName,
CostCenter,Rate,SUM(Hours)Hours,PayCategory,
ROW_NUMBER()OVER (PARTITION BY Misc_Text_Field_2,EmployeeName ORDER BY MAX(PayrollDate)) AS Seq,
MAX(TotalHours)TotalHours
FROM
MWSPayroll t

CROSS APPLY (SELECT SUM(Hours) AS TotalHours
FROM MWSPayroll
WHERE EmployeeName=t.EmployeeName
AND PayrollDate <=t.PayrollDate AND PayrollWeek =t.PayrollWeek)t1

GROUP BY PayrollWeek,EmployeeName,CostCenter,Rate,PayCategory
ORDER BY PayrollDate
),
CTE2(PayrollWeek, PayrollDate, EmployeeName, CostCenter, Hours, PayCategory, Seq, CurTotalHours,PrevTotalHours) AS
(
SELECT c1.PayrollWeek, c1.PayrollDate, c1.EmployeeName, c1.CostCenter, c1.Hours, c1.PayCategory, c1.Seq, c1.TotalHours,c2. TotalHours
FROM CTE c1
OUTER APPLY (SELECT TotalHours
FROM CTE
WHERE PayrollWeek=c1.PayrollWeek
AND Seq=c1.Seq-1
AND TotalHours<40)c2
)

SELECT PayrollWeek, PayrollDate, EmployeeName, CostCenter, Hours, PayCategory,CASE WHEN CurTotalHours>40 AND PrevTotalHours<40 THEN 40-PrevTotalHours ELSE CurTotalHours END AS TotalHours,
'REG' AS PayType
,Seq,CurTotalHours
FROM CTE2

UNION ALL

SELECT PayrollWeek, PayrollDate, EmployeeName, CostCenter, Hours, PayCategory,
CurTotalHours-40,
'OT',
Seq,
CurTotalHours
FROM CTE2
WHERE CurTotalHours>40 AND PrevTotalHours<40
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources