Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's Blog: Stored procedure for blog calendarTom Muck's Blog

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

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2024 Tom Muck | Dreamweaver Extensions