Calculating weekly averages...

Hi. I want to be able to calculate weekly averages of our report data. Here is a sample from our EmplyeeTrends table:

EmployeeID ReportDate TotalCases
2 1/1/2007 77
2 1/2/2007 63
3 1/1/2007 56All the report data is produced daily. However, my boss wants to be able to see weekly/monthly averages for each employee. Is this possible? Thanks so much for your help!select datepart(wk,ReportDate) as week
, avg(TotalCases)
from daTable
group
by datepart(wk,ReportDate)|||Yeah, but that will group weeks from different years together. I'd suggest grouping by year as well, or using this:

select dateadd(wk, datediff(wk, 0, ReportDate), 0) as week,
avg(TotalCases)
from daTable
group by dateadd(wk, datediff(wk, 0, ReportDate), 0)|||that's gorgeous, nice one blindman

:)|||thanks so much blindman and r937! those solutions work perfectly!

is it possible to find the average of the TotalCases by week as well so i can see the entire office's total cases? Does that make any sense? Thanks for your help!|||Yes, it is possible, but not advisable. You should not take averages of subtotals, as it skews your results. Weeks with fewer entries will be weighted more heavily than weeks with more entries.
It is best just to take an average across the entire office.|||i'll do that then. thanks so much for the advice!|||i'll do that then. thanks so much for the advice! wow, you don't see that very often around here...|||they're a heartless bunch, users, but the occasional good one like bla4free is why we do this|||Speak for yourself, Rudy...I do it simply for the chicks. Chicks dig database nerds.