I had nothing better to do last night so I decided to finally create one of those cheesy blog calendars. I never did understand why a blog has a calendar ("I wonder what he said on May 21, 2003"), but I did it anyway. Because I never liked those JavaScript calendar generator scripts, I decided to do it with a SQL stored procedure. SQL is fast and has built-in date functions. The added bonus is that I made it flexible enough to be able to use it with any database table that has date values, without having to do a bunch of web scripting to display the highlighting. The procedure will generate a calendar given a month and year, and if you pass it a table name and field name it will highlight those days in the calendar. For example, you can call it like this:

exec spGetCalendarWithDatesHighlighted 5, 2004

and it will generate a calendar for May 2004. If you call it like this:

exec spGetCalendarWithDatesHighlighted 5, 2004, 'myblog','blogitemdate'

it will generate a calendar for May 2004 with the days highlighted that have entries in the blogitemdate field in myblog. I guess this could be useful for other things, but I'm not sure what at this point. ;-) Here is the SQL script to generate the stored procedure:

CREATE PROCEDURE spGetCalendarWithDatesHighlighted

@month int,
@year int,
@table varchar(255) = '', -- table to match up date values, optional
@field varchar(255) = '' -- field to match

AS

DECLARE @monthstring varchar(20) -- first day of month requested
SET @monthString = cast(@month as varchar(2)) + '-01-' + cast(@year as varchar(4))

DECLARE @currentDay int -- a counter for current calendar day
SET @currentDay = 1

DECLARE @weeknumber int -- a counter for current week
SET @weeknumber = 1

DECLARE @firstDay int -- first day of month, as weekday number
SELECT @firstDay = datepart(dw,cast(@monthString as datetime))

DECLARE @lastday int -- last day of month as weekday number
SELECT @lastday = DATEPART(dd,DATEADD(DD,-1,DATEADD(MM,1,cast(@monthString as datetime))))

-- DO some error checking

IF @month > 12 OR @month < 1 BEGIN SET @month = 1 END
IF @year > 2010 OR @year < 1990 BEGIN SET @year = 2004 END

CREATE TABLE #tempCalendar -- build the calendar in a temporary table

(sunday varchar(20),
monday varchar(20),
tuesday varchar(20),
wednesday varchar(20),
thursday varchar(20),
friday varchar(20),
saturday varchar(20),
weeknumber int)

CREATE TABLE #tempCount (matchFound int)
INSERT #tempCount (matchFound) values (0)

-- Create the blank calendar using 6 weeks (maximum number of possible rows)
DECLARE @row int
SET @row = 1
WHILE @row < 7 BEGIN
INSERT INTO #tempCalendar (weeknumber, monday, tuesday, wednesday, thursday, friday, saturday, sunday)
values (@row,'','','','','','','')
SET @row = @row + 1
END

DECLARE @matchFound int
SET @matchFound = 0

DECLARE @sql varchar(1000)

DECLARE @currentDayString varchar(255)

WHILE @lastday >= @currentDay
BEGIN

if @table <> '' BEGIN -- if a table was passed to the proc, match to the table to highlight days
SET @sql = '
UPDATE #tempCount SET matchFound = (SELECT count(*)
FROM ' + @table + '
WHERE datepart(dd, ' + @field + ') = ' + cast(@currentDay as varchar) + ' AND
datepart(mm, ' + @field + ') = ' + cast(@month as varchar) + ' AND
datepart(yy, ' + @field + ') = ' + cast(@year as varchar) + ')'

EXEC (@sql)
SELECT @matchFound = matchfound FROM #tempCount
END

IF @matchFound > 0 BEGIN
SET @currentDayString = '[' + cast(@currentDay as varchar(5)) + ']' end -- if there is a match, highlight the day
ELSE begin
SET @currentDayString = cast(@currentDay as varchar(5))
END

IF @firstday = 2 BEGIN
UPDATE #tempCalendar SET monday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 3 BEGIN
UPDATE #tempCalendar SET tuesday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 4 BEGIN
UPDATE #tempCalendar SET wednesday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 5 BEGIN
UPDATE #tempCalendar SET thursday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 6 BEGIN
UPDATE #tempCalendar SET friday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 7 BEGIN
UPDATE #tempCalendar SET saturday = @currentDayString WHERE weeknumber = @weeknumber
END
IF @firstday = 1 BEGIN
UPDATE #tempCalendar SET sunday = @currentDayString WHERE weeknumber = @weeknumber
END

SET @currentday = @currentDay + 1
SET @firstday = @firstday + 1
IF @firstday = 8 BEGIN
SET @firstday = 1
SET @weeknumber = @weeknumber + 1
END
END

-- If the last week or second last week is empty, remove them (calendars can have 4-6 rows)
DECLARE @testLastWeek varchar(255)
SELECT @testLastWeek = monday + tuesday + wednesday + thursday + friday + saturday + sunday
FROM #tempcalendar WHERE weeknumber = 5
IF @testLastWeek = '' BEGIN delete from #tempCalendar
WHERE weeknumber = 5 END

SELECT @testLastWeek = monday + tuesday + wednesday + thursday + friday + saturday + sunday
FROM #tempcalendar WHERE weeknumber = 6
IF @testLastWeek = '' BEGIN delete from #tempCalendar
WHERE weeknumber = 6 END

-- display the calendar
SELECT sunday, monday, tuesday, wednesday, thursday, friday, saturday
FROM #tempCalendar c
ORDER BY weeknumber

-- Get rid of the temp tables
DROP TABLE #tempCalendar
DROP TABLE #tempCount
GO