Database Challenges

--Simon_Mendoza_TSQLChallenge_18.sql
-- Standard 'Numbers Table' CTE
WITH   N3( n ) AS ( SELECT 1 UNION SELECT 0 )
,      N2( n ) AS ( SELECT 1 FROM N3 n1 CROSS JOIN N3 n2 )
,      N1( n ) AS ( SELECT 1 FROM N2 n1 CROSS JOIN N2 n2 )
,      N0( n ) AS ( SELECT 1 FROM N1 n1 CROSS JOIN N1 n2 )
,      Num AS ( SELECT n = ROW_NUMBER() OVER (ORDER BY n) FROM N0 ) 

-- Get first day of month and a unique ID for each requested month
,      Months AS    (SELECT Mth
                     ,     Yr
                     ,     FirstDate = CONVERT(datetime,CONVERT(varchar(10),Yr * 10000 + Mth * 100 + 1),12)
                     ,     MonthId = ROW_NUMBER() OVER (ORDER BY Yr, Mth) -- get unique ref for duplicated months
                     FROM @t
                     )
-- Use numbers table to get 31 days from the first day in the month (filtered back to current month in next CTE)
,      MonthDays AS (SELECT FirstDate
                     ,       MonthId 
                     ,       MonthDay = DATEADD(day,N.n-1,M.FirstDate)
                     ,       Mth
                     FROM Months M 
                      CROSS JOIN Num N 
                     WHERE N.n <= 31 
                     )
-- Get week numbers and formatted strings for day numbers against month ID, ready for pivoting                        
,      WeekDays AS  (SELECT MonthId
                     ,      MonthDay
                     ,      WeekNumber = DATEPART(wk, DATEADD(day, @@DATEFIRST-7, MonthDay) ) -- normalize for current @@DATEFIRST 
                                          % CASE WHEN Mth = 1 THEN 52 ELSE 100 END -- roll round wk 52/53 in january,
                     ,      WeekDy     = DATEPART(weekday,DATEADD(day,@@DATEFIRST,MonthDay))  -- normalize for current @@DATEFIRST 
                     ,      FormatDay  = LEFT(CASE WHEN day(MonthDay) < 10 THEN '  ' ELSE ' ' END 
                                                  + convert(varchar(2),day(MonthDay)),3)
                     FROM MonthDays 
                     WHERE DATEDIFF(month,FirstDate,MonthDay) = 0    -- filter back to current month
                     )
-- Now get the day names in local language, and combine with above so we can pivot them together 
-- (use sentinel value of WeekNumber = -99 to split this line of day names back out later)
,     DayTitles AS  (SELECT MonthId
                     ,      WeekNumber = -99
                     ,      WeekDy     = WK.n
                     ,      FormatDay  = LEFT(DATENAME(dw,WK.n-2),3) -- 3-character day name
                     FROM Months 
                      CROSS JOIN Num WK WHERE WK.n <= 7
                     )
-- Now pivot the above values to get days 1-7 by month ID and week number                          
,     RawPivot AS   (SELECT MonthId, WeekNumber, [1],[2],[3],[4],[5],[6],[7]
                     FROM (SELECT MonthId, WeekNumber, WeekDy, FormatDay FROM WeekDays
                           UNION 
                           SELECT MonthId, WeekNumber, WeekDy, FormatDay FROM DayTitles) P
                     PIVOT (MIN(FormatDay) FOR WeekDy IN ([1],[2],[3],[4],[5],[6],[7]) ) AS PT
                     )
-- Break the day name line back out, and format to 31 characters                        
,     FormatPivot AS (SELECT MonthId
                      ,      WeekNumber
                      ,      Line = CASE WeekNumber WHEN -99 THEN 4 ELSE 6 END
                      ,      Outline =  '| '  + isnull([1],'   ') 
                                      + ' '    + isnull([2],'   ')  
                                      + ' '    + isnull([3],'   ')  
                                      + ' '    + isnull([4],'   ')  
                                      + ' '    + isnull([5],'   ')  
                                      + ' '    + isnull([6],'   ')  
                                      + ' '    + isnull([7],'   ')  + ' |' 
                      FROM RawPivot
                      )

-- Prepare some fixed separators in the correct order            
,    OutputLines AS (SELECT WeekNumber = 0, Line = 1, Outline = '+-----------------------------+' UNION ALL
					 SELECT WeekNumber = 0, Line = 3, Outline = '|=============================|' UNION ALL
					 SELECT WeekNumber = 0, Line = 5, Outline = '|-----------------------------|' UNION ALL
					 SELECT WeekNumber = 0, Line = 7, Outline = '+-----------------------------+'
					)
-- Multiply these by months, so we have each separator line per month
,    OutputMonthLines AS
					(SELECT M.MonthId
					 ,		L.WeekNumber
					 ,		L.Line
					 ,		L.Outline 
					 FROM OutputLines L 
						CROSS JOIN Months M
					)
-- Get the month names
,    MonthTitles AS ( SELECT MonthId, MonthTitle, FrontSpacing = (29 - LEN(MonthTitle))/2
					  FROM
						(SELECT MonthId 
                         ,      MonthTitle = UPPER(DATENAME(month,FirstDate)) + ' ' + CONVERT(varchar(4),Yr)
                         FROM Months ) Z
                    )
-- Center the month names
,    OutputMonthTitles AS
					(SELECT MonthId
					 ,      WeekNumber = 0
					 ,		Line = 2
					 ,		Outline = '|' + REPLICATE(' ', FrontSpacing)
											+ MonthTitle 
											+ REPLICATE(' ',29-LEN(MonthTitle)-FrontSpacing)
									+ '|'
					  FROM MonthTitles M
					 )
-- Combine all the output lines, sort them and return the output
SELECT calendar = LEFT(Outline,31) 
FROM
(   SELECT * FROM OutputMonthLines
    UNION ALL
    SELECT * FROM OutputMonthTitles
    UNION ALL
    SELECT * FROM FormatPivot
) U
ORDER BY MonthId, Line, WeekNumber	-- in month id order, then line, then week number (for line = 6)

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.