Database Challenges

--sam_camargo_tsqlchallenge_18.sql
	WITH Cal1 AS
	(	
		SELECT Mth,Yr,CONVERT(DATETIME,CONVERT(CHAR(2),Mth) + '/1/' + CONVERT(CHAR(4),Yr),101) FirstDayDate
		FROM @T
	)
    ,Cal2 AS
	(
		SELECT Mth,Yr,FirstDayDate,
		(@@datefirst + DATEPART(weekday,FirstDayDate))% 7 FirstDayNumber,
		DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(FirstDayDate), FirstDayDate)) - 1) MonthDays
		FROM Cal1
	)
	,Cal3 AS
	(
		SELECT Mth,Yr,FirstDayDate,FirstDayNumber,MonthDays,
		CASE WHEN FirstDayNumber = 0 THEN 1 ELSE 1 + 4*(7 - FirstDayNumber) END Offset,
		LEFT(REPLICATE('    ',((DATEPART(weekday,FirstDayDate) + @@datefirst - 1) % 7)) + '   1   2   3   4   5   6   7', 28) FirstRow,
		SUBSTRING('  29  30  31',1,4*(MonthDays-28))+REPLICATE('    ',31-MonthDays) + '                ' LastRowTail,
		CASE WHEN MonthDays = 31 THEN '  30  31                                            '
			WHEN MonthDays = 30 THEN  '  30                                                '
			ELSE NULL
		END LastRow
		FROM Cal2
	)
	,Cal AS 
	(
	SELECT Yr,Mth,	
		   CAST('+-----------------------------+' as CHAR(31)) c1,
		   CAST('|' + REPLICATE(' ',FLOOR((24 - LEN(DATENAME(m,FirstDayDate)))/2.0))
		       + upper(DATENAME(m,FirstDayDate)) + ' ' + convert(char(4),Yr) 
		       + REPLICATE(' ',CEILING((24 - LEN(DATENAME(m,FirstDayDate)))/2.0)) 
               + '|' as CHAR(31)) c2,
		   CAST('|=============================|' as CHAR(31)) c3,
		   CAST('| ' + LEFT(Datename(weekday,6),3) 
                + ' ' + LEFT(Datename(weekday,0),3)
                + ' ' + LEFT(Datename(weekday,1),3)
                + ' ' + LEFT(Datename(weekday,2),3)
                + ' ' + LEFT(Datename(weekday,3),3)
                + ' ' + LEFT(Datename(weekday,4),3)
                + ' ' + LEFT(Datename(weekday,5),3)
                + ' |' as CHAR(31)) c4,
		   CAST('|-----------------------------|' as CHAR(31)) c5,
		   CAST('|' + FirstRow + ' |'  as CHAR(31)) c6,
		   CAST('|' + SUBSTRING('   2   3   4   5   6   7   8   9  10  11  12  13  14',Offset,28) + ' |' as CHAR(31)) c7,
		   CAST('|' + SUBSTRING('   9  10  11  12  13  14  15  16  17  18  19  20  21',Offset,28) + ' |' as CHAR(31)) c8,
		   CAST('|' + SUBSTRING('  16  17  18  19  20  21  22  23  24  25  26  27  28',Offset,28) + ' |' as CHAR(31)) c9,
		   CAST('|' + SUBSTRING('  23  24  25  26  27  28' + LastRowTail,Offset,28) + ' |' AS CHAR(31)) c10,
		   CAST('|' + SUBSTRING(LastRow,Offset,28) + ' |' as CHAR(31)) c11,
		   CAST('+-----------------------------+' as CHAR(31)) c12
	FROM Cal3
	)
	SELECT Calendar
	FROM 
	   (SELECT Yr,Mth,c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12 FROM Cal) P
	UNPIVOT
	   (Calendar FOR Col IN (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12))AS U
	WHERE LEN(LTRIM(REPLACE(Calendar,'|',''))) > 0
	ORDER BY Yr,Mth

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.