-- parts of this taken from http://joecasella.wordpress.com/2011/05/10/tsql-tuesday-18-using-a-recursive-cte-to-create-a-calendar-table/#comment-38
CREATE PROCEDURE dbo.spGetMonthCalendar
	@MONTH INT = 1,
	@YEAR INT = 2010
AS BEGIN
 
	DECLARE @StartDate DATE
	DECLARE @EndDate DATE
 
	-- Create the start date value
	SET @StartDate = CONVERT(DATE, RIGHT('0000' + CONVERT(VARCHAR, @YEAR),4) + '-' + RIGHT('00' + CONVERT(VARCHAR, @MONTH), 2) + '-01')
	-- Create the end date
	SET @EndDate = DATEADD(M,1,@StartDate)
 
	;WITH Sales_CTE (MonthDate)
	AS
	(
	SELECT 
		@StartDate
	UNION ALL
	SELECT DATEADD(dd, 1, MonthDate) AS MonthDate 
		FROM Sales_CTE 
	WHERE 
		DATEADD(dd, 1, MonthDate) < @EndDate
	)
	SELECT 
		[1] AS sun, 
		[2] AS mon, 
		[3] AS tue, 
		[4] AS wed, 
		[5] AS thu, 
		[6] AS fri, 
		[7] AS sat
	FROM
		(
		SELECT 
			DAY(MonthDate) AS MONTHDATE,
			DATEPART(weekday, MonthDate) AS DAYOFWEEK,
			DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, MonthDate), 0), MonthDate) +1 AS ROW
			--row_number() over(order by MonthDate)/7 as ROW 
		FROM Sales_CTE 
		) AS source_table
	pivot (
		SUM(monthdate)
		FOR dayofweek in ([1],[2],[3],[4],[5],[6],[7])
		) AS pivottable
END