You will need to create a calendar table (virtual or base) that has the information on the weeks. For example, one way to create the calendar table is as follows - where I am specifying the start of the month and the day on which the week starts using variables:DECLARE @dt DATETIME; SET @dt = '20120201';DECLARE @weekstart INT; SET @weekstart = 3; -- Thursday.;WITH weeks AS( SELECT 1 AS WeekNumber, @dt AS StartDate, CASE WHEN @weekstart = DATEDIFF(dd,'19000101',@dt)%7 THEN @dt + 6 ELSE @dt + (7+@weekstart-DATEDIFF(dd,'19000101',@dt)%7)%7 END AS EndDate UNION ALL SELECT WeekNumber+1, EndDate+1, CASE WHEN EndDate + 7 >= DATEADD(MONTH,1,@dt) THEN DATEADD(dd,-1,DATEADD(MONTH,1,@dt)) ELSE EndDate + 7 END FROM weeks WHERE EndDate + 1 < DATEADD(MONTH,1,@dt))SELECT * FROM weeks;
Once you have the calendar table, you can join your own tables to the calendar table and do aggregations etc. to suit your needs. 
display monthly data into no of weeks
Posted - 2012-02-27 : 07:29:00
