Pay into Hour incrememts - 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 2008 Forums

Transact-SQL (2008)

Pay into Hour incrememts

Author

Topic

jandh98
Starting Member

10 Posts

Posted - 2014-09-09 : 10:51:31

Given this data:
InHour (int)
InMinute (int)
OutHour (int)
OutMinute (int)
Pay (float)
Date (datetime - that does not include time values)

I need to be able to divide the pay into the hour it occurred. There will also be times when the out time is less than the in time when an employee works after midnight - but will go into the same date.

TIA!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-09 : 19:37:41

please post some sample data and expected result

KH
[spoiler]Time is always against us[/spoiler]

jandh98
Starting Member

10 Posts

Posted - 2014-09-10 : 09:55:10

Thanks for replying... I think I have a lot of it figured out... what I'm stuck on now is how to calculate the overtime by hour.

(hopefully this copy and paste works)

HourInDay MinutesWorkedInHour RegPayByHour RegRate TotalMinutes TotalOTMinutes OTRate OTMinutesWorkedInHour
9 2 0.071 2.13 783 374 3.63 ?
10 60 2.13 2.13 783 374 3.63 ?
11 60 2.13 2.13 783 374 3.63 ?
12 60 2.13 2.13 783 374 3.63 ?
13 60 2.13 2.13 783 374 3.63 ?
14 60 2.13 2.13 783 374 3.63 ?
15 60 2.13 2.13 783 374 3.63 ?
16 60 2.13 2.13 783 374 3.63 ?
17 60 2.13 2.13 783 374 3.63 ?
18 60 2.13 2.13 783 374 3.63 ?
19 60 2.13 2.13 783 374 3.63 ?
20 60 2.13 2.13 783 374 3.63 ?
21 60 2.13 2.13 783 374 3.63 ?
22 60 2.13 2.13 783 374 3.63 ?
23 1 0.0355 2.13 783 374 3.63 ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-10 : 12:28:29

What are you trying to calculate? For the sample data you posted, what is the expected result?

jandh98
Starting Member

10 Posts

Posted - 2014-09-10 : 13:24:21

I have all the data except what is the OTMinutesWorkedInHour logic. I haven't wrapped my head around the fact an employee may work after midnight...yet. The data provided is for one employee.

HourInDay MinutesWorkedInHour RegPayByHour RegRate TotalMinutes TotalOTMinutes OTRate OTMinutesWorkedInHour
9 2 0.071 2.13 783 374 3.63 0
10 60 2.13 2.13 783 374 3.63 0
11 60 2.13 2.13 783 374 3.63 0
12 60 2.13 2.13 783 374 3.63 0
13 60 2.13 2.13 783 374 3.63 0
14 60 2.13 2.13 783 374 3.63 0
15 60 2.13 2.13 783 374 3.63 0
16 60 2.13 2.13 783 374 3.63 0
17 60 2.13 2.13 783 374 3.63 13
18 60 2.13 2.13 783 374 3.63 60
19 60 2.13 2.13 783 374 3.63 60
20 60 2.13 2.13 783 374 3.63 60
21 60 2.13 2.13 783 374 3.63 60
22 60 2.13 2.13 783 374 3.63 60
23 1 0.0355 2.13 783 374 3.63 1

jandh98
Starting Member

10 Posts

Posted - 2014-09-10 : 13:37:01

Digging deeper and I think my numbers on my sample for the OTMinutesWorkedInHour are incorrect. I think I'm off by an hour - but hopefully you can get the idea.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources