Database Challenges

--Muhammad_AlPasha_tsqlchallenge_18_V3.sql
WITH PossibleLastDay(day) AS
(
   SELECT CAST(28 AS TINYINT) UNION ALL
   SELECT CAST(29 AS TINYINT) UNION ALL
   SELECT CAST(30 AS TINYINT) UNION ALL
   SELECT CAST(31 AS TINYINT)
)
, LineNumbers(line_num) AS
(
   SELECT CAST(1 AS TINYINT) UNION ALL
   SELECT CAST(2 AS TINYINT) UNION ALL
   SELECT CAST(3 AS TINYINT) UNION ALL
   SELECT CAST(4 AS TINYINT) UNION ALL
   SELECT CAST(5 AS TINYINT) UNION ALL
   SELECT CAST(6 AS TINYINT) UNION ALL
   SELECT CAST(7 AS TINYINT) UNION ALL
   SELECT CAST(8 AS TINYINT) UNION ALL
   SELECT CAST(9 AS TINYINT) UNION ALL
   SELECT CAST(10 AS TINYINT) UNION ALL
   SELECT CAST(11 AS TINYINT) UNION ALL
   SELECT CAST(12 AS TINYINT)
)
, FirstAndLastDayOfMonth AS
(
   SELECT CONVERT(DATETIME, CONVERT(NVARCHAR(4), Yr) + RIGHT(N'00' + CONVERT(NVARCHAR(2), Mth), 2) + N'01') AS first_day,
          CONVERT(DATETIME, CONVERT(NVARCHAR(4), Yr) + RIGHT(N'00' + CONVERT(NVARCHAR(2), Mth), 2) +
                            RIGHT(N'00' + CONVERT(NVARCHAR(2), (SELECT MAX(day)
                                                                  FROM PossibleLastDay
                                                                 WHERE 1 = ISDATE(CONVERT(NVARCHAR(4), Yr) + RIGHT(N'00' +
                                                                                  CONVERT(NVARCHAR(2), Mth), 2) + RIGHT(N'00' +
                                                                                  CONVERT(NVARCHAR(2), day), 2)))), 2)) AS last_day
     FROM @t
)
, YearMonthAndDays AS
(
   SELECT UPPER(DATENAME(MONTH, first_day)) + N' ' + DATENAME(YEAR, first_day) AS header,
          REPLICATE(N' ', 4 * ((CASE WHEN DATEPART(WEEKDAY, first_day) + @@DATEFIRST > 7 THEN DATEPART(WEEKDAY, first_day) + @@DATEFIRST - 7
                                     ELSE DATEPART(WEEKDAY, first_day) + @@DATEFIRST
                                END) - 1)) + 
             SUBSTRING(N'   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  29  30  31',
                1, 4 * DAY(last_day) + 1) + N'                             ' AS days,
          ROW_NUMBER() OVER(ORDER BY first_day) AS row_num
     FROM FirstAndLastDayOfMonth
)
, DayOfWeekHeader AS
(
   SELECT SUBSTRING(DATENAME(DW, N'20060101'), 1, 3) + N' ' + SUBSTRING(DATENAME(DW, N'20060102'), 1, 3) + N' ' + SUBSTRING(DATENAME(DW, N'20060103'), 1, 3) + N' ' +
          SUBSTRING(DATENAME(DW, N'20060104'), 1, 3) + N' ' + SUBSTRING(DATENAME(DW, N'20060105'), 1, 3) + N' ' + SUBSTRING(DATENAME(DW, N'20060106'), 1, 3) + N' ' +
          SUBSTRING(DATENAME(DW, N'20060107'), 1, 3) AS day_0f_week
)
, Calendar AS
(
   SELECT CASE line_num
               WHEN 1 THEN N'+-----------------------------+'
               WHEN 2 THEN N'|' + LEFT(REPLICATE(N' ', (29 - LEN(header)) / 2) + header + REPLICATE(N' ', 29), 29) + N'|'
               WHEN 3 THEN N'|=============================|'
               WHEN 4 THEN N'|' + N' ' + day_0f_week + N' ' + N'|'
               WHEN 5 THEN N'|-----------------------------|'
               WHEN 12 THEN N'+-----------------------------+'
               ELSE N'|' + SUBSTRING(days, 1 + (line_num - 6) * 28, 28) + N' ' + N'|'
          END AS line,
          row_num,
          line_num
     FROM YearMonthAndDays
          CROSS JOIN
          DayOfWeekHeader
          CROSS JOIN
          LineNumbers
    WHERE line_num < 6
       OR LEN(SUBSTRING(days, 1 + (line_num - 6) * 28, 28)) > 0
       OR line_num = 12
)
SELECT line
  FROM Calendar
 ORDER BY row_num, line_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.