Database Challenges

--erik_eckhardt_tsqlchallenge_18_2.sql
/*
BeyondRelational.Com
TSQL Challenge 18
Submission #2 by Erik Eckhardt (greatly improved)


-------------------

Notes: This challenge was quite evil!

*/

;WITH Nums (Num) AS (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)
SELECT
	CASE G.Num
	WHEN 1 THEN '+-----------------------------+'
	WHEN 2 THEN Stuff('|                             |', 14 - (Len(DateName(Month,  DateAdd(Month, M.Mth - 1, 0))) + 1) / 2,  Len(DateName(Month, DateAdd(Month, M.Mth - 1, 0))) + 5, Upper(DateName(Month,  DateAdd(Month, M.Mth - 1, 0))) + ' ' + Convert(varchar(4), M.Yr))
	WHEN 3 THEN '|=============================|'
	WHEN 4 THEN '| ' + 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) + ' |'
	WHEN 5 THEN '|-----------------------------|'
	WHEN 6 THEN '| ' + Substring(
		'                             1   2   3   4   5   6   7   8   9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28 '
		+ CASE DatePart(Day, DateAdd(Day, -1, DateAdd(Month, M.Mth, DateAdd(Year, M.Yr - 1900, 0)) ))
		WHEN 28 THEN '                        '
		WHEN 29 THEN ' 29                         '
		WHEN 30 THEN ' 29  30                         '
		WHEN 31 THEN ' 29  30  31                         '
		END,
		W.Num * 28 - 4 * (DateDiff(Day, -1, DateAdd(Month, M.Mth - 1, DateAdd(Year, M.Yr - 1900, 0))) % 7), 28) + '|'
	WHEN 7 THEN '+-----------------------------+'
	END
FROM
	@t M
	INNER JOIN Nums W ON W.Num <= (DatePart(Day, DateAdd(Day, -1,  DateAdd(Month, M.Mth, DateAdd(Year, M.Yr - 1900, 0)))) + (DateDiff(Day, -1,  DateAdd(Month, M.Mth - 1, DateAdd(Year, M.Yr - 1900, 0))) % 7) + 6) / 7
	INNER JOIN Nums G ON G.Num = 6 OR W.Num = 1
ORDER BY
	M.Yr,
	M.Mth,
	G.Num,
	W.Num

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.