In our business, our crews work a lot of overtime. Calculating overtime for an employee for a work week for payroll purposes has been figured out, but I'm trying to figure out how to apply overtime hours & rates to the project those people are working on the day they hit overtime.
For example, we may have several individuals hit overtime at 2 p.m. on Thursday, so the next 3-4 hours they work would be at overtime rates that need to be assigned to that project on that day.
I could see scripting the process so that when they are clocked out at the end of the day and their total hours for the week exceeded 40, I could run a calculation that figured the time they hit 40 hours then clock them out at the regular pay rate at the time they hit overtime, and clock them back in (and out) at the overtime rate, but we would be talking tens of thousands of extra timelines each year. I have to think there is a more elegant or even efficient way.
I currently have the following tables that track pay and time:
Project_Daily_Reports::id_project with a portal - Daily_Reports_staff_timelines:id_Daily_Report /
That portal shows the Time_In, Time_Out, and daily hours for the entire crew.
Paychecks:id_staff with a portal - Paychecks_staff_timelines:id_staff /
That portal is filtered to only show a particular employee's timelines and is filtered by the WeekofYear. The layout has a status field to show if the employee hit Overtime for the week or not.
For clarity, I also have a Staff Table.
Any ideas would be appreciated.