Database Challenges

--fabien_contaminard_tsqlchallenge_18_v2.sql
With Number(fill, nm, dd) AS
-- Recursive CTE to build the short days name, anchored to 2009-12-06 which is a sunday (datepart independance here)
(
select cast(LEFT(DATENAME(weekday, CONVERT(datetime, '20091206', 112)), 3) as varchar(max)),
       1, CONVERT(datetime, '20091206', 112)
union all
select fill + ' ' + cast(LEFT(DATENAME(weekday, dd + 1), 3) as varchar(max)),
       nm + 1, dd + 1
from Number
where nm < 7
)
  ,  Struct (id, col) AS
-- Building the visual effect of the output, with an id to sort it
-- As the output as seven part, the same number of the previous CTE, it could have been reused,
-- but in my opinion it did complicate the final query way too much for the short gain
(
SELECT 1 as id, '+' + REPLICATE('-', 29) + '+' as col       UNION ALL
SELECT 2      , ''                                          UNION ALL
SELECT 3      , '|' + REPLICATE('=', 29) + '|'              UNION ALL
SELECT 4      , '| ' + fill + ' |' from Number where nm = 7 UNION ALL
SELECT 5      , '|' + REPLICATE('-', 29) + '|'              UNION ALL
SELECT 6      , ''                                          UNION ALL
SELECT 7      , '+' + REPLICATE('-', 29) + '+'              
)
  ,  Number31 (nm) AS
(
select 0
union all
select nm + 1
from Number31
where nm < 30
)
  ,  QR (Mth, Yr, Dt) AS
(  
select Mth, Yr, CONVERT(DATETIME, CAST(Yr * 10000 + Mth * 100 + 1 AS CHAR(8)), 112) + nm
from @t
    inner join Number31 
      on nm <= datediff(day, CONVERT(DATETIME, CAST(Yr * 10000 + Mth * 100 + 1 AS CHAR(8)), 112),
                        dateadd(mm, 1, CONVERT(DATETIME, CAST(Yr * 10000 + Mth * 100 + 1 AS CHAR(8)), 112)))-1
)
  ,  CTCal (Mth, Yr, Wk, Cal) AS
-- Using an old pivot style (better control on the output) to build the calendar part
-- Using datepart minus datepart from a sunday, combined with +7 and a modulo 7 also grant me language independance
-- Quite lot of concatenation and right functions to perform the desired output
(
  SELECT Mth, Yr, DATEPART(DAYOFYEAR, Dt) - (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7, 
          '|  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 0 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 1 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 2 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 3 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 4 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 5 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + '  ' + right(' ' +
         MAX(CASE (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
           WHEN 6 THEN cast(DATEPART(day, Dt) as varchar) ELSE '  ' END) , 2)
         + ' |'
    FROM QR
GROUP BY Mth, Yr, DATEPART(DAYOFYEAR, Dt) - (7 + DATEPART(weekday, Dt) - DATEPART(weekday, CONVERT(datetime, '20091206', 112)))%7
)
-- Final quey
  SELECT CAST(CASE M.id
         -- id 2 is the place for the month name, the centering effect is done by combining the remaining length divided by 2, with both floor and ceiling functions
           WHEN 2
           THEN '|'
              + SPACE(FLOOR((29-LEN(UPPER(DATENAME(mm, CONVERT(DATETIME, CAST(T.Yr * 10000 + T.Mth * 100 + 1 AS CHAR(8)), 112))) + ' ' +  CAST(T.Yr as CHAR(4))))/2.0))
              + UPPER(DATENAME(mm, CONVERT(DATETIME, CAST(T.Yr * 10000 + T.Mth * 100 + 1 AS CHAR(8)), 112))) + ' ' +  CAST(T.Yr as CHAR(4))
              + SPACE(CEILING((29-LEN(UPPER(DATENAME(mm, CONVERT(DATETIME, CAST(T.Yr * 10000 + T.Mth * 100 + 1 AS CHAR(8)), 112))) + ' ' +  CAST(T.Yr as CHAR(4))))/2.0))
              + '|'
          -- id 6 is the place for the previously computed calendar days
           WHEN 6 THEN COALESCE(C.Cal, '')
           ELSE M.col
         END AS CHAR(31)) AS col
    FROM Struct AS M
        -- the row_number is usefull if duplicates year / month are inserted in the initial dataset, it avoid to mess the output
         CROSS JOIN (SELECT row_number() over(order by Yr asc, Mth asc) AS Rn, Yr, Mth FROM @t) AS T
         LEFT OUTER JOIN CTCal AS C
           ON T.Yr  = C.Yr
          AND T.Mth = C.Mth
          AND M.id  = 6
ORDER BY T.Rn ASC, M.id ASC, C.Wk ASC

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.