TSQL Challenge 18 - Solution By roby hoye
Thu, 04/01/2010 - 05:27 — jacob
--roby_van_hoye_tsqlchallenge_18.sql
-- create output
-- => "slightly" over-engineerd solution. Although it works quite well, it doesn't really assume anything but the fact that
-- * there are up to 31 days in a month
-- * there are 7 days in a week
-- * the days in the header are based on a fixed set of known days
--
-- All other info is derived completely from the date-functions of MSSQL
--
-- Although I do like this solution for it's flexibility (I have to work with 'flexible' calendars now and then where 'assumptions' like Month 3 has 31 days are out of the question),
-- I do realize that it won't make it anywhere near the top 10 because it's too slow and to cpu-intensive in comparison with 'smarter' solutions. Yet, it was fun to make...
-- => Maybe PIVOT isn't all that scary after all =)
WITH input (mth, yr, super_seq_nbr, first_date, first_day, last_date, last_day, month_name)
as (SELECT mth = Mth,
yr = Yr,
super_seq_nbr = Yr * 16384 + Mth * 128, -- input is assumed to be unique (11 dec), otherwise add + Row_Number() OVER (ORDER BY Yr, Mth),
first_date = Convert(datetime, '01.' + Convert(varchar, Mth) + '.' + Convert(varchar, Yr), 104),
first_day = 1,
last_date = DateAdd(day, -1, DateAdd(month, +1, Convert(datetime, '01.' + Convert(varchar, Mth) + '.' + Convert(varchar, Yr), 104))),
last_day = DatePart(day, DateAdd(day, -1, DateAdd(month, +1, Convert(datetime, '01.' + Convert(varchar, Mth) + '.' + Convert(varchar, Yr), 104)))),
month_name = Upper(DateName(month, Convert(datetime, '01.' + Convert(varchar, Mth) + '.2000' , 104)))
FROM @t),
numbers (number)
as ( SELECT number = 1
UNION ALL SELECT number = 2
UNION ALL SELECT number = 3
UNION ALL SELECT number = 4
UNION ALL SELECT number = 5
UNION ALL SELECT number = 6
UNION ALL SELECT number = 7
UNION ALL SELECT number = 8
UNION ALL SELECT number = 9
UNION ALL SELECT number = 10
UNION ALL SELECT number = 11
UNION ALL SELECT number = 12
UNION ALL SELECT number = 13
UNION ALL SELECT number = 14
UNION ALL SELECT number = 15
UNION ALL SELECT number = 16
UNION ALL SELECT number = 17
UNION ALL SELECT number = 18
UNION ALL SELECT number = 19
UNION ALL SELECT number = 20
UNION ALL SELECT number = 21
UNION ALL SELECT number = 22
UNION ALL SELECT number = 23
UNION ALL SELECT number = 24
UNION ALL SELECT number = 25
UNION ALL SELECT number = 26
UNION ALL SELECT number = 27
UNION ALL SELECT number = 28
UNION ALL SELECT number = 29
UNION ALL SELECT number = 30
UNION ALL SELECT number = 31),
weekdays (wkday, wkdayname, wkdtpart)
as (SELECT wkday = number,
wkdayname = Left(DateName(dw, DateAdd(day, number, Convert(datetime, '20000101', 112))), 3) + ' ',
wkdtpart = DatePart(dw, DateAdd(day, number, Convert(datetime, '20000101', 112)))
FROM numbers WHERE number BETWEEN 1 AND 7),
weekdays_flat (day1, day2, day3, day4, day5, day6, day7)
as (SELECT [1] as day1, [2] as day2, [3] as day3, [4] as day4, [5] as day5, [6] as day6, [7] as day7
FROM (SELECT wkday, wkdayname FROM weekdays WHERE wkday BETWEEN 1 AND 7) AS src1
PIVOT (MIN(wkdayname) FOR wkday IN ([1], [2], [3], [4], [5], [6], [7])) AS pvt1),
monthdays (super_seq_nbr, number, wkday, wknbr)
as (SELECT super_seq_nbr,
number,
wkday = 1 + (((@@DATEFIRST + DatePart(dw, DateAdd(day, number - 1, first_date)))- 1) % 7), -- formula always returns 1 for Sunday, 2 for Monday, etc regardless of @@DATEFIRST
wknbr = (number - (1 + (((@@DATEFIRST + DatePart(dw, DateAdd(day, number - 1, first_date)))- 1) % 7))) -- abusing formula above to get an identifier for which week a day belongs to, not related to DatePart(wk, ..) at all !
FROM numbers, input WHERE number BETWEEN first_day AND last_day),
monthdays_flat (super_seq_nbr, wknbr, day1, day2, day3, day4, day5, day6, day7)
as (SELECT super_seq_nbr, wknbr, [1] as day1, [2] as day2, [3] as day3, [4] as day4, [5] as day5, [6] as day6, [7] as day7
FROM (SELECT super_seq_nbr, wkday, wknbr, number FROM monthdays WHERE wkday BETWEEN 1 AND 7) AS src2
PIVOT (MIN(number) FOR wkday IN ([1], [2], [3], [4], [5], [6], [7])) AS pvt2),
layout (super_seq_nbr, seq_nbr, sub_seq_nbr, txt)
as (SELECT super_seq_nbr, seq_nbr = 1, sub_seq_nbr = 1, txt = N'+-----------------------------+' FROM input
UNION ALL
SELECT super_seq_nbr, seq_nbr = 2, sub_seq_nbr = 1, txt = N'|' + Left(Space((29 - 5 - Len(input.month_name))/2) + input.month_name + N' ' + Convert(varchar, input.yr) + Space(29), 29) + N'|' FROM input
UNION ALL
SELECT super_seq_nbr, seq_nbr = 3, sub_seq_nbr = 1, txt = N'|=============================|' FROM input
UNION ALL
SELECT super_seq_nbr, seq_nbr = 4, sub_seq_nbr = 1, txt = N'| ' + day1 + day2 + day3 + day4 + day5 + day6 + day7 + N'|' FROM weekdays_flat, input
UNION ALL
SELECT super_seq_nbr, seq_nbr = 5, sub_seq_nbr = 1, txt = N'|-----------------------------|' FROM input
UNION ALL
SELECT super_seq_nbr, seq_nbr = 6, sub_seq_nbr = wknbr, txt = N'|' + Right(N' ' + ISNULL(convert(varchar, day1), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day2), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day3), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day4), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day5), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day6), N''), 4)
+ Right(N' ' + ISNULL(convert(varchar, day7), N''), 4) + N' |' FROM monthdays_flat
UNION ALL
SELECT super_seq_nbr, seq_nbr = 7, sub_seq_nbr = 1, txt = N'+-----------------------------+' FROM input)
SELECT Calendar = txt
FROM layout
ORDER BY super_seq_nbr, seq_nbr, sub_seq_nbr
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.
Subscribe
