Solution to TSQL Challenge 18 - Generate text formatted month calendars By dtodd
-- ntws133\sql2008
-- calendar
use tempdb
go
declare @t table (Mth int, Yr int)
insert @t(Mth, Yr) select 8, 2009
insert @t(Mth, Yr) select 2, 1900
insert @t(Mth, Yr) select 10,1959
select * from @t
/*
select top 100 percent *
from @t
order by
Yr, Mth
*/
;
set datefirst 7
--set datefirst 6
--set datefirst 5
--set datefirst 4
--set datefirst 3
--set datefirst 2
--set datefirst 1
;
set language french;
set language english;
-- 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
;
Sr# StartTime Reads Writes CPU Duration
--- ------------------- ----------- ----------- ----------- --------
1 Apr 8 2010 11:46PM 2849 1 656 1.742
2 Apr 9 2010 12:11AM 2839 0 780 1.870
3 Apr 9 2010 12:35AM 2839 0 733 1.824
4 Apr 9 2010 12:59AM 2839 0 624 1.867
5 Apr 9 2010 1:23AM 2839 0 702 1.744