Database Challenges

--oleg_zernov_tsqlchallenge_18.sql
;with
mdates(yr, mth, n, dw1, mname) as
(
 Select yr, mth,
   datediff(day, dateadd(month, 12 * (yr - 1900) + mth - 1, 0), dateadd(month, 12 * (yr - 1900) + mth, 0)),
   datepart(weekday, dateadd(month, 12 * (yr - 1900) + mth - 1, 0) + @@datefirst),
   upper(datename(month, dateadd(month, 12 * (yr - 1900) + mth - 1, 0))) + ' ' + convert(varchar(4), yr)
 from @t
)
-- Select * from mdates order by yr, mth
,
dow(yr, mth, d, dw, nw) as
(
  Select md.yr, md.mth, t.number,
    (t.number + md.dw1 + 5) % 7 + 1,
    (t.number + md.dw1 + 5) / 7
  from mdates md
  Inner Join master..spt_values t on md.n - t.number >= 0
    and t.type = 'P' and t.number between 1 and 31
)
-- Select * from dow order by yr, mth, d
,
cld(yr, mth, d1, d2, d3, d4, d5, d6, d7) as
(
 Select dow.yr, dow.mth,
   max(Case when dow.dw = 1 then dow.d else 0 End),
   max(Case when dow.dw = 2 then dow.d else 0 End),
   max(Case when dow.dw = 3 then dow.d else 0 End),
   max(Case when dow.dw = 4 then dow.d else 0 End),
   max(Case when dow.dw = 5 then dow.d else 0 End),
   max(Case when dow.dw = 6 then dow.d else 0 End),
   max(Case when dow.dw = 7 then dow.d else 0 End)
 from dow 
 group by dow.yr, dow.mth, dow.nw
)
-- Select * from cld order by yr, mth, d1
,
cld_final(yr, mth, sort, d1, calendar) as
(
 Select yr, mth, 1,  0, '+-----------------------------+'
 from mdates
 UNION
 Select yr, mth, 2,  0, '|' + space((29 - len(mname)) / 2) + mname + space((30 - len(mname)) / 2) + '|'
 from mdates
 UNION
 Select yr, mth, 3,  0, '|=============================|'
 from @t
 UNION
 Select yr, mth, 4,  0, '| ' + left(datename(weekday,  6), 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) + ' |'
 from mdates
 UNION
 Select yr, mth, 5,  0, '|-----------------------------|'
 from mdates
 UNION
 Select yr, mth, 6, d1,
   '|' + replace(right('   ' + convert(varchar(2), d1), 4) +
                 right('   ' + convert(varchar(2), d2), 4) +
                 right('   ' + convert(varchar(2), d3), 4) +
                 right('   ' + convert(varchar(2), d4), 4) +
                 right('   ' + convert(varchar(2), d5), 4) +
                 right('   ' + convert(varchar(2), d6), 4) +
                 right('   ' + convert(varchar(2), d7), 4), ' 0', '  ') + ' |'
 from cld
 UNION
 Select yr, mth, 7,  0, '+-----------------------------+'
 from mdates
)
Select calendar from cld_final order by yr, mth, sort, d1

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.