Database Challenges

-- File Name: Dalibor_Margotic_tsqlchallenge_18.sql
--Dalibor_Margotic_tsqlchallenge_18.sql
;WITH CTEDaysOfMonth AS(
		SELECT 1 AS Number 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 AS Number 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 AS Number 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
	), CTEMonths AS (
		SELECT 
			ROW_NUMBER() OVER(ORDER BY Yr, Mth) AS RBR
		,	Yr
		,	Mth
		,	UPPER(CONVERT(NVARCHAR(31),DATENAME(MONTH,DATEADD(month,mth-1,DATEADD(YEAR,Yr-1900,0))))) + N' ' + CONVERT(NCHAR(4), Yr) AS MthName
		,	DAY(DATEADD(DAY,-1,DATEADD(MONTH,mth,DATEADD(YEAR, Yr-1900,0)))) AS LastDayInMonth
		FROM @t		
	), CTEDays AS (
		SELECT 
			RBR
		,	Yr
		,	Mth
		,	Number 
		,	DATEPART(WEEKDAY,DATEADD(day,Number - 1 + @@DATEFIRST,DATEADD(Month, Mth-1, DATEADD(YEAR,Yr-1900,0)))) AS WeekDay
		,	DATEPART(WEEK,DATEADD(day,Number - 1 + @@DATEFIRST,DATEADD(Month, Mth-1, DATEADD(YEAR,Yr-1900,0)))) AS WeekNumber
		FROM CTEMonths AS Mth
		INNER JOIN CTEDaysOfMonth AS CTEDays ON
			CTEDays.Number BETWEEN 1 AND LastDayInMonth
	), CTEWeeks AS (
		SELECT *
		FROM CTEDays
		PIVOT (MAX(Number) FOR WeekDay IN ([1],[2],[3],[4],[5],[6],[7])) AS PVT
	), CTECalender AS (
		SELECT CONVERT(NCHAR(31),CASE WHEN OrderNumber = -3 THEN STUFF(Header,16 - LEN(MthName)/2,LEN(MthName), MthName) ELSE Header END) AS Header
		,	OrderNumber
		,	Yr
		,	Mth
		,	RBR
		FROM CTEMonths
		CROSS JOIN 
			(
			SELECT CONVERT(NCHAR(31),N'+-----------------------------+') AS Header, -4 AS OrderNumber
			UNION ALL
			SELECT N'|                             |', -3
			UNION ALL
			SELECT N'|=============================|', -2
			UNION ALL
			SELECT N'| ' 
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,6)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,0)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,1)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,2)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,3)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,4)) + N' '
				+ CONVERT(NCHAR(3),DATENAME(WEEKDAY,5)) + N' '
				+ N'|', -1
			UNION ALL
			SELECT CONVERT(NCHAR(31),N'|-----------------------------|') AS Header, 0 AS OrderNumber
			UNION ALL
			SELECT N'+-----------------------------+', 100
			) AS CR1

	)
	SELECT Header AS Calendar
	FROM
	(
		SELECT Header, OrderNumber, Mth, Yr, RBR
		FROM CTECalender
		UNION ALL
		SELECT 
			  N'|' 
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([1],N''))) + ISNULL(CONVERT(NCHAR(2),[1]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([2],N''))) + ISNULL(CONVERT(NCHAR(2),[2]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([3],N''))) + ISNULL(CONVERT(NCHAR(2),[3]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([4],N''))) + ISNULL(CONVERT(NCHAR(2),[4]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([5],N''))) + ISNULL(CONVERT(NCHAR(2),[5]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([6],N''))) + ISNULL(CONVERT(NCHAR(2),[6]) ,N'  '))
			+ CONVERT(NCHAR(4),SPACE(4-LEN(ISNULL([7],N''))) + ISNULL(CONVERT(NCHAR(2),[7]) ,N'  '))
			+ N' |', WeekNumber, Mth, Yr, RBR
		FROM CTEWeeks
	) AS REC1
	ORDER BY REC1.Yr, REC1.Mth, REC1.RBR, OrderNumber

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.