How aggregate date figures into weeks?

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

I need to create a query against a table containing a date
field, an hours figure and a description.
For each different description, the query needs to
aggregate the hours for each week (Sun-Sat)

So the data comes in like:
1/1/03 5.5 Testing
1/1/03 2.5 Testing
1/2/03 4 Enhance
1/9/03 1.5 Maint
1/10/03 2 Maint

And comes out like:
w/e 1/4/03 8 Testing
w/e 1/4/03 4 Enhance
w/e 1/11/03 3.5 Maint

Any SQL whizzes know an easy way to do this.
 
You might try a query whose SQL looks something like this:

SELECT
DateValue([Your Date Field]) + 7 - Weekday([Your Date Field]) AS [Week
Ending],
Sum([Hours]) AS [Total Hours],
[Description]
FROM
[Your Table]
GROUP BY
DateValue([Your Date Field]) + 7 - Weekday([Your Date Field]),
[Description]
 
Back
Top