return a COUNT of records by Week

[borgkling2003]

I have an asp form that submits customer records to a registration table.

eg
ID,FIRSTNAME, LASTNAME, TSTAMP

Each record is timestamped with getdate()

I need a query that returns the count of records group by WEEK...a start of a week is MONDAY...and end of a week is a SUNDAY.

eg.
START DATE, END DATE, COUNT

Does anyone knows how to do this?

 

[Elegabalus]

The easiest way to do this is to use the datepart function to group by.

Select count(*)
from table
group by datepart(wk, datefield)

The default week is Sunday to Saturday, but you can change it with SET DATEFIRST.