Time Sheet Functionality (Again...)

[FredP]

Well, I am back on to trying to create some slightly complex time tracking calculations and relationships, and was wondering if I can get some advice from all you pro's out there.

 

I will be tracking users hours per day.

 

I need to have a Daily Sum that follows these rules

from 1-8 hours is Standard Salary Day (Hours*Rate)

9 and 10 hours is overtime at (Hours*(Rate*1.5))

11 and 12 hours is overtime at (Hours*(Rate*2.0))

anything over 12 hours a day will be (Hours*(Rate*2.25))

 

But then we need a special weekend case as well.

Weekend time is only Overtime if that employee already worked their 40 hours during the week.

 

So if Weekly total is > 40

Weekend hours are (Hours*(Rate*2.0))

 

But if their weekly total is

Weekend hours are (Hours * Rate) until 40 for the week is reached.

and then over 40 is (Hours*(Rate*2))

 

First of all, can anyone understand that? Secondly can anyone help?

In our situation a week is Monday through Sunday. The first hurdle is the daily total, which I imagine is something like:

If Daily.total is > 12
  x=Daily.total-12
  y=x*(Rate*2)
If Daily.total is > 10 
  x=Daily.total-10
  y=x*(Rate*1.5)

But obviously thats not going to work.

 

Secondly, what is even more difficult for me is how to determine the "Week" total and using that to figure out accurately when weekend work is OT.

 

Thanks in advance

 

Fred

[FredP]

I should clarify that the hours that make up a day will be generated by several records in a table, that are related by the date and the employeeID.

 

IE: there is not a timesheet table with a hours field for each date. Because we have to bill hours against specific jobs, the timetracking table for a user can have 10+ records per day that adds up to 8-12 hours per day.

 

That is what makes it more difficult for me to SUM up the weekly total.

 

Fred

[Ender]

You could do something like this for daily thresholds:

 

Total Hours for Day = Sum(Job Hours::Hours Worked)

Hours Standard = min(Total Hours for Day;8)

Hours Overtime15 = min(max(Total Hours for Day - 8; 0); 2)

Hours Overtime2 = min(max(Total Hours for Day - 10); 0); 2)

Hours Overtime225 = max(Total Hours for Day - 12); 0)

 

But your weekend rates may need further definition. If the weekday total is at, say 38 hours, then is it the standard rate for the first two, then the double overtime rate for the rest?

 

Anyway, for the weekend totals, you'll need some additional structure to define a Week, so you can do aggregates by week. Once you have that, the rest is easy (either case() statements or something like what I have above).

[FredP]

But your weekend rates may need further definition. If the weekday total is at, say 38 hours, then is it the standard rate for the first two, then the double overtime rate for the rest?

 

Exactly. That is the major issue isn't it. I need to figure a way to determine the weekly total.

 

Your examples for the regular hours above is very nice, thank you.

Fred

[Techphan]

I did something similar in a DOS DB (Clarion) in 1990.

 

I tracked the following time variables (and seeing you are in California, you might be similar - but check with your own legal services person)

 

I used the following parameters to determine when to pay overtime:

1) More than 8 hours per day

2) More than 6 hours without a lunch

3) More than 40 hours per week

4) 6th and 7th day of work in a week (regardless if the employee worked 40 hours in the first 5 days or not)

 

You have outlined slightly different parameters.

 

So you at the very least you will need to create a heirarchy of rules (which rule takes precedence over the other and so on). You will also need to keep track of Days worked per week, weekend days, Hours per day, Hours per week, and running total hours of each, and then drop the hours into the appropriate pay hours: Hours @ Pay, OT Hours @ 1.5 pay, OT Hours @ 2.0 pay, and OT Hours @ 2.25.