How To Solutions

SQL Week by Week

Posted on the September 26th, 2008 under SQL by admin

This handy little SQL select statement was given to me by one of the developers I work with. It’s used to look at a date column in a SQL table and see how many rows fall within a given week from the earliest entry. For instance you could see how many subscriptions you have week by week which in turn could be used to assess the quality of your marketing methods. Let’s use that premise for the example.

So the SQL table name is subscribe and the column will be dateAdded

select convert(datetime, convert(varchar(25), dateadd(day, 1-datepart (weekday, dateadded),dateadded),107)),count(*) from subscribe group by convert(datetime, convert(varchar(25), dateadd(day, 1-datepart (weekday, dateadded),dateadded),107)) order by 1

So this SQL statement should deliver something like this…

6/9/2008	5
13/9/2008	6
20/9/2008	9

This little statement assumes Sunday is the first day of the week but this can be remedied (I’ve been informed).