Database Challenges

--erik_eckhardt_tsqlchallenge_18.sql
;WITH DayNums (Num) AS (
	SELECT -5 UNION ALL SELECT -4 UNION ALL SELECT -3 UNION ALL SELECT -2 UNION ALL SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37
), Months AS (
	SELECT
		Yr,
		Mo = DateAdd(Month, Mth - 1, DateAdd(Year, Yr - 1900, 0)),
		Anchor = DateAdd(Day, -DateDiff(WeekDay, -1, DateAdd(Month, Mth - 1, DateAdd(Year, Yr - 1900, 0))) % 7, DateAdd(Month, Mth - 1, DateAdd(Year, Yr - 1900, 0)))
	FROM @t
), Days AS (
	SELECT
		M.Mo,
		Row = DateDiff(Day, Anchor, DateAdd(Day, D.Num - 1, M.Mo)) / 7,
		Col = DateDiff(Day, Anchor, DateAdd(Day, D.Num - 1, M.Mo)) % 7,
		Label = 
			CASE WHEN D.Num < 1 OR D.Num > DatePart(Day, DateAdd(Day, -1, DateAdd(Month, 1, M.Mo))) THEN '    '
			ELSE Right('  ' + Convert(varchar(2), D.Num), 3) + ' '
			END
	FROM
		Months M
		INNER JOIN DayNums D ON D.Num BETWEEN 1 - DateDiff(Day, -1, Mo) % 7 AND (DateDiff(Day, Mo, DateAdd(Month, 1, Mo)) + DateDiff(Day, -1, Mo) % 7 + 6) / 7 * 7 - DateDiff(Day, -1, Mo) % 7
)
SELECT
	CASE WHEN G.ID IN (1, 3, 4, 5, 7) THEN G.Chars
	WHEN G.ID = 2 THEN Stuff(G.Chars, 14 - (Len(DateName(Month, P.Mo)) + 1) / 2,  Len(DateName(Month, P.Mo)) + 5, Upper(DateName(Month, P.Mo)) + ' ' + Convert(varchar(4), DatePart(Year, P.Mo)))
	WHEN G.ID = 6 THEN '| ' + [0] + [1] + [2] + [3] + [4] + [5] + [6] + '|'
	END
FROM
	(
		SELECT * FROM Days
		PIVOT (Max(Label) FOR Col IN ([0], [1], [2], [3], [4], [5], [6])) X
	) P RIGHT JOIN (
		SELECT 1, '+-----------------------------+'
		UNION ALL SELECT 2, '|                             |'
		UNION ALL SELECT 3, '|=============================|'
		UNION ALL SELECT 4, '| ' + Left(DateName(WeekDay, -1), 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) + ' |'
		UNION ALL SELECT 5, '|-----------------------------|'
		UNION ALL SELECT 6, NULL
		UNION ALL SELECT 7, '+-----------------------------+'
	) G (ID, Chars) ON G.ID = 6 OR P.Row = 0
ORDER BY
	P.Mo,
	G.ID,
	P.Row

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.