for each week - count items
Posted on 2013-12-24
I have a table with a column of type datetime
I want to find how often a record is in this table per week.
example of the records:
Id=111, DateColumn= '09-12-2013'
Id=112, DateColumn= '10-12-2013'
Id=113, DateColumn= '10-12-2013'
Id=114, DateColumn= '16-12-2013'
Id=115, DateColumn= '17-12-2013'
Id=116, DateColumn= '24-12-2013'
Id=117, DateColumn= '26-12-2013'
Id=118, DateColumn= '26-12-2013'
Id=119, DateColumn= '27-12-2013'
I want to return all weeks from 01-01-2013 to 31-12-2013 and how often an event happens each week. I would like to return it like this:
StartDate: '08-12-2013', EndDate: '14-12-2013 Count: 3
StartDate: '15-12-2013', EndDate: '21-12-2013 Count: 2
StartDate: '22-12-2013', EndDate: '28-12-2013 Count: 4
How is best to do this?