Get sum of hours since previous Monday for an employee
I am trying to create a calculation field on a time log database but I've run into a problem.
Picture a table with the following fields. EmployeeID, Hours (time data type) DateWorked, HourlyRate, HourlyOvertimeRate.
OK, that's just the basics but here's what I have done. I created a calculation field that returns the Monday before the DateWorked value to determine the week's start date. Now I want a calculation that will return the sum of hours for that employee since the value of the Previous Monday field. Then I can determine if the new record puts the employee into overtime (over 40 hours since Monday) and calculate the cost of this hour entry based on his straight time hourly rate and overtime hourly rate.
Keep in mind there may be several records for an employee in a single day broken down by different jobs.
So say an employee has 39 hours already since monday and a new record is created for 4 hours. I need to create a calculation field that will do something like 1 hour times straight time rate + 3 hours times overtime rate. (with of course consideration for minutes as the hour field is a time datatype.
Any suggestions would be greatly appreciated!