-- *** Note: Switch results to text mode before running this to see the proper formatting.

DECLARE @T TABLE (MTH INT, YR INT)
INSERT @T		SELECT  8,2009
	UNION ALL	SELECT  2,1900
	UNION ALL	SELECT  2,2015
	UNION ALL	SELECT  2,2000
	UNION ALL	SELECT 10,1959
	UNION ALL	SELECT  9,2008
	UNION ALL	SELECT  1,1975
	UNION ALL	SELECT  5,1987
	
/*
=============================================================================================
CREATE DATE: 12/02/2009
 CREATED BY: SETH PHELABAUM
    PURPOSE: TSQL Challenge 18 - Generate text formatted month calendars
     ISSUES: 
      NOTES: Switch results to text mode before running this to see the proper formatting.
Revision History
Date	 By		Change Made
-------- ---  	-------------------------------------
=============================================================================================
*/
; WITH 
	-- Tally table Gen
t1 AS (SELECT 1 N UNION ALL SELECT 1 N), 
t2 AS (SELECT 1 N FROM t1 x, t1 y),        
t3 AS (SELECT 1 N FROM t2 x, t2 y),        
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y),
	-- Create dates out of the Mth + Yr
Dates AS (SELECT CAST(CAST(Yr as char(4)) + RIGHT('0' + CAST(Mth as varchar(2)),2) + '01 00:00:00' as datetime) SDate
			FROM @t),
	-- Use Tally to Populate Days for those months
DatesRN AS (SELECT SDate, ROW_NUMBER() OVER (ORDER BY SDATE) RN
			FROM Dates),
	-- Get a list of all calendar days in the months to be displayed and the day of the week they fall on.
Dates2 AS (SELECT N CalDate, RN, CASE DATENAME(dw,DATEADD(D,N-1,SDate))
									WHEN DATENAME(DW,-1) THEN 1
									WHEN DATENAME(DW, 0) THEN 2
									WHEN DATENAME(DW, 1) THEN 3
									WHEN DATENAME(DW, 2) THEN 4
									WHEN DATENAME(DW, 3) THEN 5
									WHEN DATENAME(DW, 4) THEN 6
									ELSE 7
									END DWI
			FROM DatesRN, Tally
			WHERE DATEADD(d,N,SDate) > SDate 
				AND DATEADD(d,N,SDate) <= DATEADD(m,1,SDate)),	
	-- Figure out the calendar Offset for the month and the Week Number based on that offset
Dates3 AS (SELECT D.*, CEILING((CalDate + Offset) / 7.00) Wk
			FROM Dates2	D
				INNER JOIN (SELECT RN, DWI-1 OffSet
							FROM Dates2
							WHERE CalDate = 1) D2 ON D.RN = D2.RN),
	-- Create the MonthNames
Months AS (SELECT RN, UPPER(DATENAME(m,sdate)) + ' ' + CAST(YEAR(sdate) as char(4)) MName
			FROM DatesRN),
	-- Pad the MonthNames to Create the Header
MHead AS (SELECT RN, '|' + RIGHT(REPLICATE(' ',FLOOR((29.0-LEN(MName))/2.0)) + MName + REPLICATE(' ',CEILING((29.0-LEN(MName))/2.0)),29) + '|' MHead 
			FROM Months),
	-- Days of the Week Header
DW AS	(SELECT '| ' + 	LEFT(DATENAME(DW,-1),3) + ' ' +
						LEFT(DATENAME(DW, 0),3) + ' ' +
						LEFT(DATENAME(DW, 1),3) + ' ' +
						LEFT(DATENAME(DW, 2),3) + ' ' +
						LEFT(DATENAME(DW, 3),3) + ' ' +
						LEFT(DATENAME(DW, 4),3) + ' ' +
						LEFT(DATENAME(DW, 5),3) + ' |' DHead),
	-- Pivot the Calendar Data
Cal AS (SELECT RN,Wk,
			MAX(CASE WHEN DWI = 1 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Sun',
			MAX(CASE WHEN DWI = 2 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Mon',
			MAX(CASE WHEN DWI = 3 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Tue',
			MAX(CASE WHEN DWI = 4 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Wed',
			MAX(CASE WHEN DWI = 5 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Thu',
			MAX(CASE WHEN DWI = 6 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Fri',
			MAX(CASE WHEN DWI = 7 THEN RIGHT('   ' + CAST(CalDate as varchar(2)),4) ELSE '    ' END) 'Sat'
		From Dates3
		GROUP BY RN, Wk),
	-- Concatenate the Calendar Data
CalConcat AS ( SELECT RN, Wk, '|' + Sun + Mon + Tue + Wed + Thu + Fri + Sat + ' |' CalConcat
				FROM Cal)

SELECT CA.Calendar
FROM DatesRN DRN
	CROSS APPLY	(	-- Put it all Together
			SELECT 'A' O1,	1 O2,	'+' + REPLICATE('-',29) + '+' Calendar UNION ALL
			SELECT 'B',		1,		MHead FROM Mhead WHERE RN = DRN.RN UNION ALL
			SELECT 'C',		1,		'|' + REPLICATE('=',29) + '|' UNION ALL
			SELECT 'D',		1,		DHead FROM DW UNION ALL
			SELECT 'E',		1,		'|' + REPLICATE('-',29) + '|' UNION ALL
			SELECT 'F',		WK,		CalConcat FROM CalConcat WHERE RN = DRN.RN UNION ALL
			SELECT 'G',		1,		'+' + REPLICATE('-',29) + '+' Comb
				) CA 
ORDER BY DRN.RN,O1,O2
	/* Insert your query above */