(SQL) Grouping by End of week

Question

I have a report which needs to total up the number of registrations per week, over the last ten weeks and display it as

"Week Ending October 10th, 2009" 500"

"Week Ending OCtober 3rd, 2009" 400"

"Week Ending September 26, 2009" 1000"

etc...

Here is my query:

SELECT Count(*) as [Total]
      ,Week = DateAdd(day, -1 * datepart(dw, CONVERT(varchar,CreateDate,101)),    CONVERT(varchar,CreateDate,101))

FROM aspnet_membership WITH (nolock)

WHERE CreateDate BETWEEN CAST(CONVERT(varchar, DATEADD(ww, -10, DATEADD(dd, -(DATEPART(dw, GETDATE()) - 1), GETDATE())), 101) AS DATETIME)  AND
           DateAdd(day,  -1 *  datepart(dw, CONVERT(varchar,Getdate(),101)), CONVERT(varchar,GetDate(),101))
GROUP BY DateAdd(day, -1 * datepart(dw, CONVERT(varchar,CreateDate,101)), CONVERT(varchar,CreateDate,101))
ORDER BY [Week] DESC

The between clause is doing a between 08/02/2009 and 10/10/2009 but the results don't contain any records for the week of October 10th, even though there are thousands in the DB.

I'm guessing I'm either grouping by something incorrectly, or something.

Any help would be greatly appreciated.

Answer 1

Try the DATEPART function:

SELECT 
    datepart(week,createdate) as WeekNumber
,   count(*) as RowsPerWeek
FROM aspnet_membership WITH (nolock)
GROUP BY datepart(week,createdate)

You can calculate the end of the week using min(createdate). Would work just as well with max(createdate) or even avg(createdate).

SELECT
    DATEADD(dd, 7-(DATEPART(dw, min(createdate))), min(createdate))
      as EndOfWeek
,   count(*) as RowsPerWeek
FROM aspnet_membership WITH (nolock)
GROUP BY datepart(week,createdate)

This would return the saturday at the end of the week (the time part can still vary.)
I see from your question you already know how to format the long date.

Answer 2

Your DateAdd function is actually finding the Saturday of the previous week, i.e. for 10/13/2009 it's returning 10/10/2009, for 10/7/2009 and 10/8/2009 it's returning 10/3/2009, etc. A simple +7 should fix it:

DATEADD(dd, - DATEPART(dw, CONVERT(varchar, CreateDate, 101)) + 7, CONVERT(varchar, CreateDate, 101))