SELECT Sum(MyCost), DateDiff(d, 0, MyDate) / 7

FROM MyTable

GROUP BY DateDiff(d, 0, MyDate) / 7

Replace 0 if your week starts on a different day. You won't get a row for
weeks that don't have an entry. Join to a Dates table for a more complete
solution.

[quoted text, click to view]

there will ALWAYS be 53 "weeks" per year (52 * 7 = 364); the solution
depends on what exactly the business logic requires ... you might have
another look at what David W posted and flesh it out a bit more ... you may
have preferred mine because it exposed the date boundaries but that doesn't
mean it's better, or even suitable for your requirements

as David suggested, you could work up a dates table, which might look
something like this:

CREATE TABLE weeksTable (
theDate DateTime,
xWeek int)

-- I'll leave you to figure out how to populate it but the data would look
like:

1/1/2007 1
1/2/2007 1
.....
1/8/2007 2
....
1/15/2007 3

and then JOIN it ... that way you can cut your weeks off at the end of the
calendar year or simply make them continuous regardless of year boundaries
..... again, depends on the business logic

something like this should work for the JOIN:

SELECT b.xWeek, SUM(a.Cost)
FROM myTransactions a
JOIN weeksTable b ON a.trxDate = b.theDate
GROUP BY b.xWeek

this is not a new problem; you can probably Google up a complete solution
in reasonably short order if you can't make sense of the input here ...
maybe SQLServerCentral.com has something ... good luck

L



[quoted text, click to view]