Learn how to a build a cloud-first strategyRegister Now

x
Solved

# getting payroll data

Posted on 2011-05-04
Medium Priority
216 Views
I have a database with employee work hours.  They are eligible for extra pay on statutory holidays if they have worked 15 or more of the previous 30 days.  I have developed the code to query which employees are eligible, but i would like to also do the calculation to see how many hours they are to be paid for.  The result is the number of hours/days they have worked in the past 30.  (i.e. if they worked 110 hours in 16 days over the past 30 then the average would be 6.87 hours)

the table is named 'paydata' and the fields are EmpNumber (employee number), EmpName (employee name), Hours (hours worked each day) and WorkDate

``````SELECT paydata.EmpNumber, paydata.EmpName, Sum(paydata.Hours) AS SumOfHours
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>[enter threshold]))
ORDER BY paydata.EmpName;
``````
0
Question by:Gerw
• 4
• 4

LVL 44

Expert Comment

ID: 35693309
So what do you do with an average of 6.87 hrs/day?  It would be nice to know if the extra pay is a percentage.  Do you have a tblHolidays?  Is paydata multi-year?

0

Author Comment

ID: 35693653
I calculate this for each statutory holiday.  For example, the next stat holiday is July 1st for us, so I will do an inquiry asking for the number of days worked between June 1st and June 30th with a 'threshold' of 14.  Anyone with 15 days or more will show up in the inquiry together with their total hours.

The addition to the inquiry which I am trying to build would calculate the average hours which gets entered into the payroll as 'stat hours' and the payroll system (totally separate) calculates their pay owing based on the normal rate of pay.

There is no tblHolidays, although it might be a nice feature.  We have 10 stat holidays a year here in Canada so it is not an onerous job to do the inquiry.  If we had such a table, the inquiry could check to see if a holiday falls within a pay period and then does the 30 day calculation and separates the overtime pay for anyone eligible who happened to work on the stat holiday.  (our business is a restaurant and we are open seven days a week.)

Just to be clear - to be eligible for stat holiday pay or overtime pay, the employee must have worked 15 of the previous 30 days prior to the holiday and of course overtime is only paid if the employee worked on the holiday and was eligible.
0

LVL 44

Accepted Solution

GRayL earned 2000 total points
ID: 35693798
Then you need a PayFactor field.  I used 1.5, and 1.0 - you should use what ever your situation dictates.

``````SELECT paydata.EmpNumber, paydata.EmpName,
Sum(paydata.Hours) AS SumOfHours,
Iif(Count(paydata.hours))>[enter threshold]),1.5,1.0) as PayFactor
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>[enter threshold]))
ORDER BY paydata.EmpName;
``````
0

Author Comment

ID: 35693893
Sorry, I don't understand the 1.5 and 1.0 PayFactor.  What I need is for the query to divide the total hours worked in the past 30 days by the number of days the employee worked so long as it was greater than 14.
0

LVL 44

Expert Comment

ID: 35694461
And when you have an employee who was sick for the past 30 days, that logic generates a divide-by-zero error.  I still do not grasp where you are coming from - and I designed the system that billed the prime over \$140M over 15 years.  Rather than specifics, can you 'tell' us what happens, and what you need?
0

Assisted Solution

Gerw earned 0 total points
ID: 35694471
GrayL - I have managed to come up with the following based on your response.  Using your structure and applying it to what I needed here is my solution

SELECT paydata.EmpNumber, paydata.EmpName, Sum(paydata.Hours) AS SumOfHours, Avg(paydata.Hours) AS AverageHours
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>10))
ORDER BY paydata.EmpName;

I am temporarily using the number 10 for the number of days for the cutoff, but I will change that to 14 in the final version.  Thanks for the help.  ( this all had to be rebuilt after a crash caused by an external hard drive which contained my backup files.  a bizarre event)  I will pursue the idea of a tblStatHolidays that you asked about.  ideally the query would check for a Stat Holiday date and if it found one, would count back 30 days to determine who is eligible for stat pay or overtime pay.  Then it would separate the overtime hours for that day for eligible employees and provide the average hours as above for those employees who are eligible for stat holiday pay.
0

Author Comment

ID: 35694558
I meant to award the 500 points to GrayL for his help in arriving at my solutilon.
0

LVL 44

Expert Comment

ID: 35694640
Thanks, I think?
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
###### Suggested Courses
Course of the Month20 days, 21 hours left to enroll