Create Weekly Query Summing Hours And Overtime
Posted on 2011-02-11
One of the pains of getting new software is losing some features that you don't want to lose. I am trying to create a query that takes an employee's time (from Sunday to Saturday) and do a sum.
The trick is, trying to calculate the overtime - and I will explain why. I have a table that has the employee's number, the date they clocked in and the time they clocked in and out. I used the DATEDIFF and am able to calculate the hours worked for the day. Overtime is not calculated until they have worked 40 hours that week. So if a person worked the following hours during the week: 8, 8, 9, 7, 8 - That is 40 hours. The 9 hour day is not considered 8 hours with 1 hour of overtime.
Now if a person worked the hours: 10, 10, 10, 10, 8 (48 hours for the week), that would mean on the end of day 4, they worked 40 hours. Day 5 would then be put in the "Overtime" field and not the "Standard Hours" field.
And to make things even more complicated, they need to show the time in decimal format. So if someone worked 4 hours and 30 minutes of overtime it wouldn't show as 4:30 but rather 4.5.
I have been working on this for the past week and can't seem to find a solution. Any help or suggestions is greatly appreciated!