Database Challenges

--paul_chang_tsqlchallenge_18.sql
-- We create two CTEs
-- One is just the same as @t
-- with extra columns that are used in calculations
-- The other is a static table
-- used to populate calendar rows
with t as (
	select
		t.Mth,
		t.Yr,
		-- We use a sortorder to make sure the rows come back in order
		t.Yr * 10000 + t.Mth * 100 as sortorder,
		-- We calculate the first day of the month by adding to date 0
		-- We don't use conversions from strings because of complications
		-- in different localities
		dateadd(month, t.Mth - 1, dateadd(year, t.Yr - 1900, cast(0.0 as datetime))) as firstday,
		-- The offset for the first day of the month is the same as
		-- the day of the week, minus one
		-- But because of localization, we cannot depend on datepart(weekday, ...)
		-- So we calculate modulo 7 from 1900-01-07, which is Sunday
		datediff(day, cast(6 as datetime), dateadd(month, t.Mth - 1, dateadd(year, t.Yr - 1900, cast(0 as datetime)))) % 7 as offset,
		-- The last day is the same as the first day
		-- plus one month minus one day
		datepart(day, dateadd(day, -1, dateadd(month, 1, dateadd(month, t.Mth - 1, dateadd(year, t.Yr - 1900, cast(0 as datetime)))))) as lastday
	from
		@t as t
),
-- There can be between four and six rows on a calendar
-- depending on the month and year.  The offset is the
-- number of days to add for that week.
week as (
	          select 1 as week,  0 as offset
	union all select 2 as week,  7 as offset
	union all select 3 as week, 14 as offset
	union all select 4 as week, 21 as offset
	union all select 5 as week, 28 as offset
	union all select 6 as week, 35 as offset
)
select
	Calendar.Calendar
from
	(
-- Top border
select '+' + replicate('-', 29) + '+' as Calendar, t.sortorder +  1 as sortorder from t
union all
-- Calendar title
-- We have to center, so we take half of the remaining space
-- We floor on the left, ceiling on the right
-- So that, if the remaining space is odd,
-- we center it a little left
select
	'|' +
	replicate(' ', floor(29.0 / 2.0 - len(datename(month, firstday)) / 2.0 - 0.5 - len(rtrim(t.Yr)) / 2.0)) +
	upper(datename(month, firstday)) +
	' ' +
	rtrim(t.Yr) +
	replicate(' ', ceiling(29.0 / 2.0 - len(datename(month, firstday)) / 2.0 - 0.5 - len(rtrim(t.Yr)) / 2.0)) +
	'|' as Calendar,
	t.sortorder +  2 as sortorder from t
union all
-- Border between title and days of week
select '|' + replicate('=', 29) + '|' as Calendar, t.sortorder +  3 as sortorder from t
union all
-- Days of the week
-- We can take any week, as long as it begins with Sunday
-- So we use 1900-01-07 through 1900-01-13
select
	'| ' + left(datename(weekday, cast( 6 as datetime)), 3) +
	' '  + left(datename(weekday, cast( 7 as datetime)), 3) +
	' '  + left(datename(weekday, cast( 8 as datetime)), 3) +
	' '  + left(datename(weekday, cast( 9 as datetime)), 3) +
	' '  + left(datename(weekday, cast(10 as datetime)), 3) +
	' '  + left(datename(weekday, cast(11 as datetime)), 3) +
	' '  + left(datename(weekday, cast(12 as datetime)), 3) +
	' |'
	as Calendar,
	t.sortorder +  4 as sortorder from t
union all
-- Border between days of week and calendar days
select '|' + replicate('-', 29) + '|' as Calendar, t.sortorder +  5 as sortorder from t
union all
-- Calendar days
-- We use the week offset and the offset from the first day
-- to calculate the calendar, seven days at a time
select
	'|  ' +
		case
			when week.offset - t.offset + 1 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 1)
			when week.offset - t.offset + 1 between 10 and t.lastday then       rtrim(week.offset - t.offset + 1)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 2 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 2)
			when week.offset - t.offset + 2 between 10 and t.lastday then       rtrim(week.offset - t.offset + 2)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 3 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 3)
			when week.offset - t.offset + 3 between 10 and t.lastday then       rtrim(week.offset - t.offset + 3)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 4 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 4)
			when week.offset - t.offset + 4 between 10 and t.lastday then       rtrim(week.offset - t.offset + 4)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 5 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 5)
			when week.offset - t.offset + 5 between 10 and t.lastday then       rtrim(week.offset - t.offset + 5)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 6 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 6)
			when week.offset - t.offset + 6 between 10 and t.lastday then       rtrim(week.offset - t.offset + 6)
			else '  '
		end +
	'  '  +
		case
			when week.offset - t.offset + 7 between 1 and 9          then ' ' + rtrim(week.offset - t.offset + 7)
			when week.offset - t.offset + 7 between 10 and t.lastday then       rtrim(week.offset - t.offset + 7)
			else '  '
		end +
	' |' as Calendar,
	t.sortorder +  5 + week.week as sortorder
from
	t
	inner join week
	on	week.offset - t.offset < t.lastday
union all
-- Bottom border
select '+' + replicate('-', 29) + '+' as Calendar, t.sortorder + 12 as sortorder from t
) as Calendar
order by sortorder

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.