Monthly Calendars in Transact SQL

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, so I wrote one that did this.

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

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?