1. Registered User
Join Date
Jul 2004
Posts
156

Happy New Year All!

I'm having an issue with something that only arises at most twice a year. I'm finding labor cost by summing the hours per week. 40 and under gets regular pay rate, 40+ gets OT pay rate. I do this by using the function DatePart("ww",[Date],1,3). I use 3 (first full week of year is first week) so that there will be no "partial weeks" numbered as a week, as there is with the default first week with January 1. For example, at the beginning of next year, using the first week is the one with January 1, there will be only one day in the week #1 of 2005 and only 6 days in week #53 of 2004. Therefore, to sum up hours by week, I have to use an option that will only give whole weeks.

The problem with this is that it will leave a partial week at the beginning of the year that will count as part of the last year's last week. For example, the end of this year coming up and the beginning of next year will be tied together as the 52nd week of 2004 (12/26/04-1/1/05). Then 1/2/05 will start week 1 of 2005.

Essentially, this would put some labor hours in the previous year than when they were actually worked. If I wanted to pull the labor cost for a particular year, how could I include the hours from the last few days in December for the purpose of seeing if I'm over 40 hours for that week? See what I'm saying? Because those hours would be included in any Overtime hour calculations. Am I approaching this from the wrong angle of using week #'s to sum labor hours?

This seems to be a complicated problem that surely thousands of programmers have had to deal with. Every payroll program would have to deal with this. Your help on this would be GREATLY appreciated!
Last edited by DocX; 12-29-04 at 04:30.

2. Registered User
Join Date
Jul 2004
Posts
156
bumping for the Holiday lull.

3. Registered User
Join Date
Jul 2004
Posts
156
bumping again...just because I don't have an answer.

4. Registered User
Join Date
Dec 2004
Location
Sunshine Coast, Australia
Posts
52
SELECT SUM(HOURS) FROM table WHERE DATE BETWEEN DATE1 AND DATE2

Thats how you could do it, personally I do everything mathmatical behind VBA

5. Registered User
Join Date
Jul 2004
Posts
156
Umm...no. Thanks for the attempt. But it's far from what I need. I wish it were that easy. I'm working with queries, anyway, which don't use VBA. You have to use SQL. See, I need to sum hours that aren't included in the date range, as well, based on a week-by-week basis.

6. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
I'm just not understanding your problem ... Perhaps you're making this too complicated ... It sounds like you're trying to calculate hours chronically using financial rules ...

7. Registered User
Join Date
Jul 2004
Posts
156
Here it is in all its basicity: I need to figure up the cost per week per employee for any given date range. My query is based on the date range they put in. So, a start date in the middle of a week will not include the hours from the beginning of that week, which will cause problems when summing up the employee's work hours to determine if OT was worked. I sum up hours by grouping the records (filtered by date range) by week #. I don't know how to include the hours from the beginning of the week without also including them in with the labor cost.

Example: Boss wants labor cost of hourly employees for dates 1/5/05 - 1/31/05. 1/2/05 - 1/4/05 will not be included in the query as I have it now. However, the hours worked for those days will be necessary to determine when OT (over 40) will hit, which will drastically change the labor cost for that week.

If you have any more questions, let me know. Thanks for givin' me an ear!

8. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
Ok ... How to you determine which hours constitute OT? Is it after 40? After 8 per day? After a certain time of the day?

As for the query: I don't see any way of making a "generic" query to accomplish this ... You might with a 2 parter ... Run a query to calc your labor cost for the week and then run a query to subtract out either the prior year's amount or the current year's amount (depending on with side of the equation you're examining - also depending on your OT rules) ...

9. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
You have submitted two mutually exclusive criteria. First you say that no partial weeks are to be allowed in the calculation, then you say you must calculate partial weeks so they are not counted twice.

Am I missing something?

10. Village Idiot
Join Date
Jul 2003
Location
Michigan
Posts
1,941
Here's something that should get you pointed in the right direction:

PARAMETERS StartDate DateTime;
SELECT employeeid, SUM(BEGTTLHOURS) AS TTLHOURS, SUM(WTDTTLHOURS) AS WTDHOURS
FROM (
SELECT employeeid, 0 as WTDTtlHours, Sum(hoursworked.hours) AS BegTtlHours
FROM employees INNER JOIN hoursworked ON employees.employeeid = hoursworked.employeeid
WHERE (((DatePart("ww",[date],1,3))=DatePart("ww",[StartDate],1,3)))
group by employeeid
union
SELECT employeeid,Sum(hoursworked.hours) AS WTDTtlHours, 0 as begttlhours
FROM employees INNER JOIN hoursworked ON employees.employeeid = hoursworked.employeeid
WHERE (((DatePart("ww",[date],1,3))=DatePart("ww",[StartDate],1,3)) AND ((hoursworked.date)>=[startdate]))
group by employeeid)
group by employeeid;

I created a table with 3 columns: employeeid, date, and hours
The parameter supplied to the query, which you'll be asked for when you open the query (StartDate) is the beginning date for which you are totalling hours.
The first half of the UNION subquery will supply total hours worked by employee for the week containing that date (BegTtlHours).
The second half of the UNION subquery will supply hours worked in that week where the date is >= the StartDate paremeter.

You'll have to add a second parameter to your query (EndDate) and perform the same steps for the last week. Last, you'll have to total the hours for all weeks in between. I think this is possible in one giant query, with multiple union subqueries.

Hope that helps get you started.
Last edited by RedNeckGeek; 01-03-05 at 11:24.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•