471,594 Members | 1,880 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,594 software developers and data experts.

Payroll model.

Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?





Dec 14 '06 #1
8 2807
"John Sitka" <jo*******@REMOVEhotmail.comwrote in message
news:Oj**************@TK2MSFTNGP03.phx.gbl...
>
What approach could be used to make sense? it seems that there just is not
enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s)
booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the
company is overpaying
or underpaying, would you be satifisfied, with that kind of system?
This isn't a C# or even a programming question - it's a business question.

///ark
Dec 14 '06 #2
Hello John,

How is the time recorded? Is it by time clock? If so then the rate
should just be matched to the clocked time. If it is just a number of
hours then then the rates should be apportioned to the relevant time
ranges or if it is just a total number of hours then the rates should
just be apportioned pro rata. I'm afraid your scenario raises more
questions than it answers.

Regards,
Gary
http://www.garyshort.org/

John Sitka wrote:
Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?
Dec 14 '06 #3
Thanks, I didn't notice.
"Mark Wilden" <mw*****@communitymtm.comwrote in message news:%2****************@TK2MSFTNGP02.phx.gbl...
"John Sitka" <jo*******@REMOVEhotmail.comwrote in message news:Oj**************@TK2MSFTNGP03.phx.gbl...
>>
What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?

This isn't a C# or even a programming question - it's a business question.

///ark

Dec 14 '06 #4
Thanks Gary,
Each record has a double value (8.0) with a flag to identify which type.
>standard
shiftpremium
doubletime
doubletimepremium
The flag are manually set at time of record as before the work is begun it is known that
the shift is of a certain type.

A typical day for an individual
would look like
Job-activitycode-hours-flag
A1-154-4-standard
A1-158-2-standard
A2-154-4-standard.

It's the distribution of the overtime I can't get even the simplest of logic worked out for.
I realise there is not enough here but being stuck I know lots of people here have integrated
payroll applications and probably have a huge amount of good ideas.

<ga**@garyshort.orgwrote in message news:11**********************@l12g2000cwl.googlegr oups.com...
Hello John,

How is the time recorded? Is it by time clock? If so then the rate
should just be matched to the clocked time. If it is just a number of
hours then then the rates should be apportioned to the relevant time
ranges or if it is just a total number of hours then the rates should
just be apportioned pro rata. I'm afraid your scenario raises more
questions than it answers.

Regards,
Gary
http://www.garyshort.org/

John Sitka wrote:
>Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?

Dec 14 '06 #5

Store the hours worked as one field:

UserWorkWeeks
----------------------------
UserWorkWeekId
UserId
WeekStartDate
HoursWorked

And then use a weekly process to do payroll which can do the
appropriate calculations and then store the calculated values back in
the database in a separate table:

CalculatedPayroll
---------------------------
UserWorkWeekId
PayTimeId
Hours
Rate
Accouting/Financial/Payroll applications are a rare breed where it's
often appropriate or necessary to break some fundamental DB rules in
favor of auditing, history, and performance (i.e., normally you never
store calculated values in a database).

HTH,

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking Mid/Sr. .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Thu, 14 Dec 2006 15:32:40 -0500, "John Sitka"
<jo*******@REMOVEhotmail.comwrote:
>Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?



Dec 14 '06 #6
PS

"John Sitka" <jo*******@REMOVEhotmail.comwrote in message
news:O2**************@TK2MSFTNGP02.phx.gbl...
Thanks Gary,
Each record has a double value (8.0) with a flag to identify which type.
>>standard
shiftpremium
doubletime
doubletimepremium
The flag are manually set at time of record as before the work is begun it
is known that
the shift is of a certain type.

A typical day for an individual
would look like
Job-activitycode-hours-flag
A1-154-4-standard
A1-158-2-standard
A2-154-4-standard.

It's the distribution of the overtime I can't get even the simplest of
logic worked out for.
I realise there is not enough here but being stuck I know lots of people
here have integrated
payroll applications and probably have a huge amount of good ideas.
Assuming you want to distribute the overtime evenly then the amount of
overtime would be proportional based on the total hours for the week, e.g 20
Standard and 30 Doubletime would mean that of the 10 hours of overtime 4
hours would be StandardOT (20/50 x 10) and 6 hours would be doubletimeOT
(30/50 * 10).

Note that this "even distribution" of overtime is actually the law in some
states. For example in New York State the DOL law states that the overtime
rate be no less than 1 1/2 times the average rate paid for the week. For
example if the standard rate is $10/hour and the employee again worked 20
standard and 30 doubletime hours the employees average rate is $800 / 50
hours = $16/hour. Therefore OT on the 10 hours has to be paid at a minimum
of $24/hour.

PS
>


<ga**@garyshort.orgwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
>Hello John,

How is the time recorded? Is it by time clock? If so then the rate
should just be matched to the clocked time. If it is just a number of
hours then then the rates should be apportioned to the relevant time
ranges or if it is just a total number of hours then the rates should
just be apportioned pro rata. I'm afraid your scenario raises more
questions than it answers.

Regards,
Gary
http://www.garyshort.org/

John Sitka wrote:
>>Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are
only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40
hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is
not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s)
booked would be used as the
type for the overtime. Does that makes sense, could it be argued that
the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?

Dec 15 '06 #7
Thank you PS.
Great advice, and I now see two methodologies

1.) The stack method where I could sequentially count all types up to 40 hours as not overtime from the bottom of the
stack upwards. Then the types over and above that 40 hours retain their type but change to the overtime case (OT) of that type

2.)The proportional method you just described.

I need to ask the business about this base assumption you mentioned...
Assuming you want to distribute the overtime evenly then the amount of overtime would be proportional based on the total hours for
the week
Much appreciated.


"PS" <ec***********@hotmail.comwrote in message news:uE****************@TK2MSFTNGP04.phx.gbl...
>
"John Sitka" <jo*******@REMOVEhotmail.comwrote in message news:O2**************@TK2MSFTNGP02.phx.gbl...
>Thanks Gary,
Each record has a double value (8.0) with a flag to identify which type.
>>>standard
shiftpremium
doubletime
doubletimepremium
The flag are manually set at time of record as before the work is begun it is known that
the shift is of a certain type.

A typical day for an individual
would look like
Job-activitycode-hours-flag
A1-154-4-standard
A1-158-2-standard
A2-154-4-standard.

It's the distribution of the overtime I can't get even the simplest of logic worked out for.
I realise there is not enough here but being stuck I know lots of people here have integrated
payroll applications and probably have a huge amount of good ideas.

Assuming you want to distribute the overtime evenly then the amount of overtime would be proportional based on the total hours for
the week, e.g 20 Standard and 30 Doubletime would mean that of the 10 hours of overtime 4 hours would be StandardOT (20/50 x 10)
and 6 hours would be doubletimeOT (30/50 * 10).

Note that this "even distribution" of overtime is actually the law in some states. For example in New York State the DOL law
states that the overtime rate be no less than 1 1/2 times the average rate paid for the week. For example if the standard rate is
$10/hour and the employee again worked 20 standard and 30 doubletime hours the employees average rate is $800 / 50 hours =
$16/hour. Therefore OT on the 10 hours has to be paid at a minimum of $24/hour.

PS
>>


<ga**@garyshort.orgwrote in message news:11**********************@l12g2000cwl.googlegr oups.com...
>>Hello John,

How is the time recorded? Is it by time clock? If so then the rate
should just be matched to the clocked time. If it is just a number of
hours then then the rates should be apportioned to the relevant time
ranges or if it is just a total number of hours then the rates should
just be apportioned pro rata. I'm afraid your scenario raises more
questions than it answers.

Regards,
Gary
http://www.garyshort.org/

John Sitka wrote:
Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?


Dec 15 '06 #8
Thank you Samuel
I would fully intend to do this.
But that information will truely be retained by the program
which generates the EFT (payroll file transfer) to the bank.
So the calculated values are retainable durable in a number of ways.

But I don't see where your suggestion maintains the type of pay

What that EFT program dosen't do however is help with the
>>appropriate calculations
If I was to enter 5 consecutive 10 hour days in this Payroll program.
it would not mark the fifth day as overtime, but it would require it coded
as such when generating the pay for that week. The ERP system on the other hand
would code that fifth day entry appropriately but the ERP system does not have a workable interface
to record thousands of time entries a day. However I built a program that makes that kind of entry
possible and then posts to the ERP system. I can then batch the coded aggregates back to the payroll
program.

It's the "appropriate calculations" for the aggregates I don't know, even what they are called.
With the advice given here, I have a clearer picture of what is involved.

I realize now I'm struggling with two different interpretations of overtime.
Daily overtime OR weekly averaged overtime and I fear this company has somehow got
a defacto hybrid of that in play. 10 hours working one day a week does not grant you
2 hours overtime but 10,8,8,8,8 days would. Obviously this is straight forward.

But for example

Monday 10 standard
Tuesday 8 standard
Wednesday 8 standard
Thursday 8 standard
Friday 8 doubletime

can be interpreted three ways

(overfill)
34 standard
6 doubletime
2 doubletimeOT

OR

(daily)
32 standard
2 standardOT
8 doubletime

OR

(proportional)
32 standard
8 doubletime
1.6 hours standardOT
..4 hours doubletimeOT
So you need either a proportional distribution or an overfill condition. With the overfill condition.
each separate time unit needs individual investigation to determine if it came before or after the overfill
condition was reached.


"Samuel R. Neff" <sa********@nomail.comwrote in message news:gt********************************@4ax.com...
>
Store the hours worked as one field:

UserWorkWeeks
----------------------------
UserWorkWeekId
UserId
WeekStartDate
HoursWorked

And then use a weekly process to do payroll which can do the
appropriate calculations and then store the calculated values back in
the database in a separate table:

CalculatedPayroll
---------------------------
UserWorkWeekId
PayTimeId
Hours
Rate
Accouting/Financial/Payroll applications are a rare breed where it's
often appropriate or necessary to break some fundamental DB rules in
favor of auditing, history, and performance (i.e., normally you never
store calculated values in a database).

HTH,

Sam
------------------------------------------------------------
We're hiring! B-Line Medical is seeking Mid/Sr. .NET
Developers for exciting positions in medical product
development in MD/DC. Work with a variety of technologies
in a relaxed team environment. See ads on Dice.com.

On Thu, 14 Dec 2006 15:32:40 -0500, "John Sitka"
<jo*******@REMOVEhotmail.comwrote:
>>Hi,

There are four pay types

standard
shiftpremium
doubletime
doubletimepremium

each hour a person books can be one of these types

Now when the payroll is calculated once a week the system
needs additional to be spoon fed to know which hours are overtime
so it has an additional set of overtime pay types

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

Obviously you can't record these types during the week because they are only meaningful after
the results are in for the whole week.

So as an example

an individuals hours for the week look like this

12 standard
12 shiftpremium
12 doubletime
12 doubletimepremium

They have worked 48 hours this week. If overtime kicks in after 40 hours.
they need to have 8 hours distributed to these codes

standardOT
shiftpremiumOT
doubletimeOT
doubletimepremiumOT

What approach could be used to make sense? it seems that there just is not enough information.
Maybe a bucket analogy. LIFO the last (in terms of time line) type(s) booked would be used as the
type for the overtime. Does that makes sense, could it be argued that the company is overpaying
or underpaying, would you be satifisfied, with that kind of system?




Dec 15 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Randi | last post: by
reply views Thread by zexx | last post: by
1 post views Thread by kgatchell2001 | last post: by
4 posts views Thread by Mark T. | last post: by
reply views Thread by sajithamol | last post: by
4 posts views Thread by jnce | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by Anwar ali | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.