11-09-04, 04:27 #1Registered User
- Join Date
- Jul 2004
Unanswered: Grouping By Week-Beginning and End of Year
Hello guys and dolls:
This seems like a problem that a lot of people dealing with payroll or labor costs or something related would have to consider.
What I'm having an issue with is exactly how to deal with weekly hours and weeks of the year. See, I need to calculate total hours for a week because anything 40 hours is overtime. This amount over 40 uses the OT pay rate and up to 40 uses the regular pay rate.
For a query with dates running through a week that starts in one year and then, when the year ends, switches over to the next year in the same week, how would you calculate the overtime on this? All the date functions that consider weeks in Access that I've found will not combine the "extra" days at the end of the year (i.e., week 53) and the days at the beginning (i.e., part of week 1).
How can you handle this? This would greatly help me! Thanks in advance!DocX
The teachings of God's Begotten: 2 John 1:9
11-09-04, 09:34 #2Grand Poobah
- Join Date
- Sep 2003
There are numerous ways of handling this ...You can setup a separate table with Begin and End dates for each pay week ... You can do somewhat the same with only a beginning week also ... How to get the crossyear week to calculate?
SELECT SUM(Hours) FROM SomeTable WHERE ((EmployeeID=XXX) AND ((StartDay>#SomeStartDate#) AND (EndDay<DateAdd('d',6,SomeStartDate))));
This query is more generic in that it handles ANY date range. You supply the startdate ... I assume you can handle the parsing of the hours into it's regular versus OT components ...Back to Access ... ADO is not the way to go for speed ...