Tom Muck's Blog: Stored procedure for blog calendar![Tom Muck's Blog](/blog/images/tom.gif)
News and Views
Stored procedure for blog calendar
Friday, June 11, 2004 4:58:29 PM
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
Category tags: Dreamweaver
Posted by Tom Muck
Add comment |
View comments (0) |
Permalink
|
Trackbacks (0)
|
Digg This
Before posting comments or trackbacks, please read the posting policy.