Divide Dates into Weeks and Months

[Matt]

Hi All,

I have a table that has 3 fields:

OT_DATE
WEEK
MONTH

The purpose of this table is to calculate overtime worked per employee
per week. I join to the table to an attendance table on the date and
then use this tbale to group by week (so that I can caluclate the hrs
over 40 for each employee). Is there any way to do this other than
manually adding dates to this table. It is no big deal, but I didnt
know how other people did this.

 

[Guest]

You can group by week using the attendance table alone in the query without
the need for the second table. To group by week for each employee for
instance the query's GROUP BY clause would be something like:

GROUP BY YEAR([AttendanceDate]), DATEPART("ww", [AttendanceDate]),
[EmployeeID]

Or you could group first by employee if you wanted each employee's data
grouped together.

Similarly to group by month:

GROUP BY YEAR([AttendanceDate]), MONTH([AttendanceDate]), [EmployeeID]

The group-level on YEAR is necessary as otherwise the same weeks or months
from different years would be grouped together.

Ken Sheridan
Stafford, England