/*
I have been seized with the novel idea of writing something useful in a BLOG entry.
My eye was caught by the rather fetching calendar in the top right of the BLOG. I've had to write Transact SQL code that produces calendars like this in production systems before now; Most often when one has to get from a user the beginning date or end date for a report, or for doing complex drill-down reports. Normally, when writing a web application, I add the CSS/HTML as part of the stored procedure so that I can specify the values that are POSTed back on a user-click. (See 'Eric Meyer on CSS' ISBN 0-7357-1245 for everything you need to know about CSS rendering).
Here is the bare-bones routine
CREATE PROCEDURE spCalendar
--draw a calendar as a result set.
-- you can specify the month if you want
@Date datetime=NULL--any date within the
-- month that you want to calendarise.
/*
eg. spCalendar '1 Jan 2006'
spCalendar '16 Jun 2006'
*/
AS
SET nocount ON
DECLARE @sequence TABLE
(
number INT
)
--create utility sequence of a week
INSERT INTO @sequence(number) SELECT 1
INSERT INTO @sequence(number) SELECT 2
INSERT INTO @sequence(number) SELECT 3
INSERT INTO @sequence(number
) SELECT 4
INSERT INTO @sequence(number) SELECT 5
INSERT INTO @sequence(number) SELECT 6
INSERT INTO @sequence(number) SELECT 7
SET DATEFIRST 2
--nail down the start of the week
DECLARE @MonthLength INT --number of days in the month
DECLARE @MonthStartDW INT --the day of the week that the...
--...month starts on
--if no date is specified, then use the current date
SELECT @Date=COALESCE(@date,GETDATE(
))
--get the number of days in the month and
--the day of the week that the month starts on
SELECT @MonthLength=DATEDIFF(DAY,CONVERT(CHAR(11),@Date,113),
CONVERT(CHAR(11),DATEADD(MONTH,1,@Date),113)),
@MonthStartDW=DATEPART(dw,'1 '+
SUBSTRING( CONVERT(CHAR(11),@Date,113),4,8
))
SELECT
[Mon]=MAX(CASE WHEN DAY=1 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Tue]=MAX(CASE WHEN DAY=2 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Wed]=MAX(CASE WHEN DAY=3 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Thu]=MAX(CASE WHEN DAY=4 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Fri]=MAX(CASE WHEN DAY=5 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Sat]=MAX(CASE WHEN DAY=6 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END),
[Sun]=MAX(CASE WHEN DAY=7 AND monthdate BETWEEN 1 AND @MonthLength
THEN CONVERT(CHAR(2),monthdate) ELSE '' END)
FROM
(--roll out the day number and week number so
--you can then do a pivot table of the results
SELECT [day]=DayNo.Number, [week]=Weekno.number,
[monthDate]=(DayNo.Number
+ ((Weekno.number-1)*7))-@MonthStartDW
FROM @Sequence DayNo
CROSS JOIN @sequence weekNo
)f
GROUP BY [week]--so that each week is on a different row
HAVING SUM(CASE WHEN DAY=1 AND monthdate BETWEEN 1
AND @MonthLength THEN monthdate ELSE 0 END)>0 OR week=1
--take out any weeks on the end without a valid day in them!
I once had the challenge of a boss who insisted on his financial summary reports in a calendar or diary format, as though one was chalking up the figures on a printed calendar. I rather sneered at first, because it was different, and IT people don't instinctively like 'different', but it actually made a lot of sense with something like a daily revenue figures where there was a natural variation according to the day of the week and the part of the month. One naturally provides totals by week and day of week as well as the previous months summary.
It is for this sort of 'diary-style' reporting where a calendar routine becomes useful, but this is also where writing the HTML/CSS within the stored procedure really pays dividends, heretical though it may sound. It allows you to identify the functional components of the result. As you'd pull in a stylesheet to determine the actual rendering, the designer still has control over the appearance of the page.
I'd be interested to know of other approaches to this. Is this the simplest way of doing it, or is my obsession with cross-joins beginning to tell?
*/