Database Challenges

-- File Name: david_todd_tsqlchallenge_18.sql
--david_todd_tsqlchallenge_18.sql
-- logic is to select each of the elements
-- First header
-- Month and year 
-- second header
-- days of week
-- third header
-- month days
-- footer line
-- and use the value sort-order to place them in the correct sequence.
-- WeekStart is used to place the individual weeks in the correct order

select 
	--c.FirstOfMonth
	--, c.SortOrder
	--, c.WeekStart
	--,
	c.Calendar
from 
	(
	select 
		t.FirstOfMonth
		, 1 as SortOrder
		, t.FirstOfMonth as WeekStart
		, '+-----------------------------+' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select 
		t.FirstOfMonth
		, 3 as SortOrder
		, t.FirstOfMonth as WeekStart
		, '|=============================|' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select 
		t.FirstOfMonth
		, 5 as SortOrder
		, t.FirstOfMonth as WeekStart
		, '|-----------------------------|' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select 
		t.FirstOfMonth
		, 7 as SortOrder
		, t.FirstOfMonth as WeekStart
		, '+-----------------------------+' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select
		t.FirstOfMonth
		, 2 as SortOrder
		, t.FirstOfMonth as WeekStart
		, '|' 
		+ space( floor(( 31 - 2 - 1 - 4 - len( datename( month, dateadd( month, t.Mth - 1, 0 )))) / 2.0 ))
		+ upper( datename( month, dateadd( month, t.Mth - 1, 0 ))) 
		+ ' '
		+ cast( t.Yr as char( 4 ))
		+ space( ceiling(( 31 - 2 - 1 - 4 - len( datename( month, dateadd( month, t.Mth - 1, 0 )))) / 2.0 ))
		+ '|' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select
		t.FirstOfMonth
		, 4 as SortOrder
		, t.FirstOfMonth as WeekStart
		-- not specially pretty, but works
		, '| ' 
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 1
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 2
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 3
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 4
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 5
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ left( datename( weekday, 
			dateadd( 
				day
				, - ( datepart( weekday, t.FirstOfMonth ) + @@datefirst - 1 ) % 7 + 6
				, t.FirstOfMonth
				) )
			, 3 ) + ' '
		+ '|' as Calendar
	from 
		(
		select 
			Yr
			, Mth
			, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
		from @t ) as t

	union all

	select 
		PivotTable.FirstOfMonth
		, 6 as SortOrder
		, PivotTable.WeekStart
		-- cludge as the columns rotate depending on the setting of datefirst
		, case @@datefirst
			when 7 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ ' |' 
			when 1 then 
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ ' |' 
			when 2 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ ' |' 
			when 3 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ ' |' 
			when 4 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ ' |' 
			when 5 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ ' |' 
			when 6 then
				'|' 
				+ right( isnull( '    ' + convert( varchar( 2 ), [2] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [3] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [4] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [5] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [6] ), '    ' ), 4 ) 
				+ right( isnull( '    ' + convert( varchar( 2 ), [7] ), '    ' ), 4 )
				+ right( isnull( '    ' + convert( varchar( 2 ), [1] ), '    ' ), 4 )
				+ ' |' 
		end as Calendar
	from
		(
		select 
			--dateadd( 
			--	day
			--	, - ( datepart( weekday, dateadd( day, n.Number, t.FirstOfMonth )  ) + @@datefirst - 1 ) % 7
			--	, dateadd( day, n.Number, t.FirstOfMonth )
			--	) as WeekStart
			dateadd( 
				day
				, - ( datepart( weekday, dateadd( day, n.Number, t.FirstOfMonth )  ) + @@datefirst - 1 ) % 7
				, dateadd( day, n.Number, t.FirstOfMonth )
				) as WeekStart
			--, left( datename( weekday, dateadd( day, n.Number, t.FirstOfMonth )  ), 3 ) as DWeekDayName
			, datepart( weekday,  dateadd( day, n.Number, t.FirstOfMonth )  ) as DWeekDay
			, datepart( day, dateadd( day, n.Number, t.FirstOfMonth )) as DDay
			, t.FirstOfMonth
		from 
			(
			select 
				Yr
				, Mth
				, dateadd( month, Mth - 1, dateadd( year, Yr - 1900, 0 )) as FirstOfMonth
			from @t ) as t
		cross join 
			(
			select Number 
			from master..spt_values 
			where 
				Type = 'P' 
				and Number >= 0
				and number < 31
			) n
		where
			month( dateadd( day, n.Number, t.FirstOfMonth ) ) = Mth
		) as p
	pivot
	(
	sum( DDay )
	--for DWeekDayName in ( [Sun], [Mon], [Tue], [Wed], [Thu], [Fri], [Sat] ) 
	for DWeekDay in ( [1], [2], [3], [4], [5], [6], [7] ) 
	) as PivotTable
	) c
order by
	c.FirstOfMonth
	, c.SortOrder
	, c.WeekStart

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.