Help with complex pay roll system

[Vaughan]

I need help with a complex pay roll system I am working on. Sorry for the long post: I'll describe the business, my solution implementation, and my challenge.

I have a table "StaffBookings" where each record is the amount of time a person works, and their pay rate for the period. It has staffid, start time, end time, hourly rate. (Work periods will not cross midnight.)

The complexity is that on a given day a staff person may have multiple StaffBookings records, with different pay rates. There is technically no limit to the number of StaffBookings records per day for a person. The business rules are:

1) The full pay period for the day is the minimum start time until the maximum end time.

2) The person gets the highest rate of pay for any given period.

3) Any breaks between pay periods are paid at the person's lowest rate for that day.

Examples (not exhaustive of every possibility):

Bruce

1/1/2011, 9:00 am to 5:00 pm, $20 per hour

1/1/2011, 2:00 pm to 4:00 pm, $30 per hour

Pay = 5x$20 + 2x$30 + 1x$20

Taniya

1/1/2011, 10:00 am to 12:00 pm, $20 per hour

1/1/2011, 1:00 pm to 5:00 pm, $30 per hour

Pay = 2x$20 + 1x$20 + 4x$30

The algorithm to work out the pay: for a given staff and day, list all of the times for all the slots.

Taniya: 10:00, 12:00, 1:00, 5:00

Use these times to create contiguous periods:

10:00 - 12:00

12:00 - 1:00

1:00 - 5:00

For each period calculate the maximum pay rate. If there is no pay rate then use the lowest rate for the day.

----

Assuming that you all agree that this is a good algorithm, here is how I want to implement it:

I could script a process that loops through the StaffBookings records, gathers the times, then creates "TimePeriods" records that then relate back to the StaffBookings records and calculate the money to pay. I could, except I really don't want to make this into a "manually" calculated process because this will be unforgiving for data changes etc and it needs to be "run" and after it's run any changes to the StaffBookings data won't be reflected in the pay roll. That scares me.

Instead I want to work out a way to create one "PayRollDay" record for each staff/day that calculates the pay dynamically.

In pseudo code, I imagine the implementation would be like this:

A PayRollDay record gets created for each staff, for each date. (The StaffBookings records are related to the PayRollDay through a PayRollDayID field so a StaffBookings record can only be included in a pay roll once.)

PayRollDay --> StaffBookings

Each PayRollDay record needs to work out the time slots and pay rates for the related StaffBookings records, then sum the amounts.

If a limit to the number of StaffBookings records was imposed then it could be done with multiple hard-coded relationships between PayRollDays and StaffBookings (one for each time slot) but I don't want to build a system with arbitrary hard-coded limits, especially for something as critical as pay roll.

OTTH this is my first pay roll system, and if you folks experienced with pay roll reckon this is a bad idea and I should just "manually" process them then let me know!

[comment]

I think you could do this even without a PayRollDays table, just by summarizing the StaffBookings table. In any case, it comes down to the same algorithm:

For each staff/day, calculate:

• total of time worked (you should calculate the duration in each record, then sum them up);

• total of time on break = maximum of end time - minimum of start time - total of time worked;

• amount to pay = total of time worked * maximum of rate + total of time on break * minimum of rate.

you folks experienced with pay roll

I believe folks experienced with payroll (namely LaRetta) would tell you not to touch it. :laugh2:

[Vaughan]

A simple summary won't work because the staff isn't being paid for the total of the StaffBookings.

Bruce

1/1/2011, 9:00 am to 5:00 pm, $20 per hour

1/1/2011, 2:00 pm to 4:00 pm, $30 per hour

pay ≠ 8x$20 + 2x$30 (simple summing of the records)

pay = 5x$20 + 2x$30 + 1x$20 (slicing and dicing)

[RodSierra]

We do similar with what we call a time card table. A time card is posted one record for each day for each active employee. Some information, such as normal start time, normal end time and in some cases break periods are pulled in from the employee record.

In and out entries are generated via odbc to a timeclock table, or in the case of professionals logged on their own via data entry. With human entry its easier to have them log in and out in seperate fields, in the case of automatic time clock, we use only the time and ID, then via calcuations determine the work periods.

We use a project Id in the time clock table that references another table that establishes the rate based on the hours worked and day.If for instance the day is a holiday the rate may be different, or if the times are from a range outside the normal work hours the rate may be different.

At the end of the day expected hours must reconcile with logged hours.

I'd recommend you set up your calculations to take into account time periods that overlap days, it makes life a lot easier in the long run.

Another consideration is how do you handle fractions of an hour. Unless requested to do otherwise we convert to tenths of an hour. There are some established rules out there that set how a tenth is calculated and it seems to work for us the best, but we've had exceptions for some customers in the past also.

You should also think about how tolerant the system will be to early time in and out numbers. We set a tolerance that says if a person logs in or punches in no earlier than this tolerance their normal start time is calculated from the employee record start time. Conversely the same applies to punch or log out times. We allow for no variation from expected to posted times, so these tolerances must be accounted for when calculating the actual work times. From the time card we generate a pay record that summarizes hours worked at what rate, this is posted on the financial side.

Hope this makes sense, blasted it out as I'm kinda busy today.

[Vaughan]

Thanks Rod, great advice especially the time overlapping days.

[comment]

Wait a minute - I missed the part about overlapping entries (doesn't make much sense, does it?).

[Vaughan]

The overlapping entries add to the complexity, but are not unusual.

Basically, staff are "on call" for higher duties. The overlapping entries are the higher duties and they are at a higher pay rate. The lower rate could be considered a retainer, if you will.

It's the need to slice and dice that makes this challenging, and fun.

BTW, how does this sound:

For a given number of entries, the maximum number of "periods" are 2n-1. This means that it may be possible to re-cycle the StaffBookings records and get them to handle 2 of the periods each thus avoiding having to create "period" records.

Can you see where I'm going here? I want this to work itself out without having to run a script what manually creates the period records that sit between the PayRollDay record and the StaffBookings.

(I'm not doing this because it's a challenge, I'm doing it because it will massively reduce maintenance.)

[LaRetta]

Hi Vaughan,

Do I understand your need? A person gets paid for total hours at work (max end - min start ). Pay all 'preferred' rates, subtract 'preferred hours' and pay the remaining hours at base rate?

1) Subtract Min Start from Max End = total hours worked for the day

2) Find Min Rate and use as relational key <> to split data

3) Split data into two groups (those at min rate and those not), please see attached

The calcs are a bit ugly (I would probably use summaries as Michael suggested) but I wanted you to see the concept before I spent more time going this direction. And then of course, flag your records when posting month-end or after payroll generation to prohibit modification (through security) and also include a flag to ‘archive’ prior periods then include that flag in the relationship filter so the speed doesn't dog-down on you.

I hope this is moving you a bit forward anyway ...

... and yes, I would recommend against designing an inventory, payroll or accounting system. But I've done them which is why I recommend against it LOL.

StaffBookings.zip

[Vaughan]

LaRetta, many thanks for replying.

There can be more than 2 StaffBookings record, they need not overlap, or they could be completely overlapping. Eg, senior person is sick so backup acts up for the whole night.

I just had a walk outside and came up with a possible solution. It might be a bit ugly. ;)

[LaRetta]

"There can be more than 2 StaffBookings record,"

Did I say my file had a limit on number of bookings?

[Vaughan]

Wow... I'm looking at your demo file and I'm seriously impressed. :)

[Vaughan]

LaRetta: forget my post about the 2 record limitation, it was sent before I saw your demo file. My apologies for inferring your demo had limitations or you mis-understood my requirements.

That file is seriously good.

Wow. ;)

[comment]

Basically, staff are "on call" for higher duties. The overlapping entries are the higher duties and they are at a higher pay rate. The lower rate could be considered a retainer, if you will.

I think there are some assumptions here that need to be verified:

1. There will be no overlap of "higher duty" entries;

2. At least one of the entries will be at "retainer rate".

[Vaughan]

The "retainer rate" is, by definition, the minimum of the staff's rates for that day.

The other item I have to think about and get back to you. Logically I'd say no overlap; but ideally it would be better if this was not a restriction.

[comment]

The "retainer rate" is, by definition, the minimum of the staff's rates for that day.

This is a very strange arrangement. Suppose Adam's regular rate is $20. One day Adam comes to work at 9:00 and immediately gets called to work for an hour at $40 rate. Then he sits on his ass until 4:00 pm when he gets called again to work for another hour at $40 rate. For this day, Adam gets paid 8 * 40 = $320.

However, had Adam worked one more hour at his regular rate, he would be paid only $200 (2 * 40 + 6 * 20).

[Vaughan]

Adam would have a staff booking record from 9:00 to whatever at $20 per hour.

[NLR]

Hi Vaughan,

Here's my contribution to the discussion. Also my demo file.

If I'm correct in understanding the problem, the way I worked it out is on the basis that:

Each staff person works a certain number of hours a day whether they're actually "working" or "on call". Their day is composed of idle time and working time.

Their minimum/basic/guaranteed pay for any given day is calculated by multiplying their total time at work by their "On-call" hourly rate.

On top of that they gain extra pay by working any number of "periods" within the scope of that day, with each increment of pay being worked out by the "Difference-in-HrlyRate" multiplied by that particular "period".

To separate the 2 types of "mode" (for want of a better word), I used a field called "WorkMode". It takes one of two values (from a value list) being "OnCall" or "Working". Whichever value appears affects calculations such as "HrlyRateDifference", which is either zero when a person's mode is "OnCall", or when they're "working" - it's the difference between the bigger-hourly-rate and the On-call-rate.

From that I calculate the "PaymentThisPeriod"; and from that the "PaymentThisDay", the latter being the sum of related records from a simple relationship called "SamePerson_SameDay"

In essence, I have (for a given person on a given day), one record tagged "OnCall" with its StartTime and EndTime running the full "extent" of a working day (for example from 9am to 5pm). Then additional records within that "extent" tagged "Working", showing periods whenever a person actually does work.

There's one script used only for updating the display.

Regards

Ralph Learmont

DailyPay.zip

[comment]

Adam would have a staff booking record from 9:00 to whatever at $20 per hour.

Is this a new rule? It doesn't follow from the premises stated earlier. There's something that feels wrong about the entire setup. I would go back and examine how the records are being created. It seems there are two types of entries - and not enough distinction between them.

[LaRetta]

Adam would have a staff booking record from 9:00 to whatever at $20 per hour.

Does every day that a staff person shows up generate a 'base record' for that day which is their default hours (9-5 in example) and their base wage ($20)? Is this why you say they will have one? If a staff doesn't have a 'base record' do they not get paid for that day? Is this what you mean by 'not enough distinction between them', Michael?

As Ralph points, out, the base isn't necessary if just to pull their base wage. But it WOULD be necessary if it determined whether staff got paid at all for the day. Does the staff generate the base record when they show up? Auto-generation of payroll records (in advance) would make me nervous. If a base record is used, what if all the new daily 'preference' records totals more than the base at the end of the day?

Ralph's file is very good!

[comment]

Is this what you mean by 'not enough distinction between them', Michael?

I meant roughly the same thing you see in Ralph's file as "workmode" - except I would take it even further. We are only guessing here, but I would expect a staff member to have a default rate in their staff record - and they would be paid this rate for the entire duration of their ATTENDANCE on that day. If they did some special work during this time, they would be paid extra = duration of special work * (special rate - default rate). And I would put those entries in their own table, separate from attendance (this would also make it easy to prevent overlapping entries of special work).

[Vaughan]

Many thanks Ralph, I have not had time to check the demo, but will do so soon.

OK the exact scenario:

The staff are either teachers or assistants.

Teachers get assigned to classes: say two classes 9:00 to 12:00 and 1:00 to 5:00 pm but they get paid for the period 12:00 to 1:00 pm as well. Generally the pay rate will be the same for both classes but it could be different.

Assistants get assigned a much longer period, say 8:30 to 5:30 pm. The assistant rate is lower than the teacher rate. Should the need arise the assistant may be required to step in and do a teacher's 1:00 to 5:00 class. If so they will be paid at the teacher rate for this period.

It is acknowledged that this business arrangement is overly complex and it is in the process of being significantly simplified.

As always, your expertise is much appreciated. ;)