> Hello all, I am re-designing a database driven calendar application.
> I want to support recurring events (such as a weekly meeting) and I
> was looking for a few design suggestions. Basically, I want a user
> to be able to enter an event and specify that it happens on a
> repeating schedule: every week, once a month, etc. When a user
> views the calendar I want these to appear, but I do not want to
> have to enter a record into the database for each occurrence of that
> event. In other words, if an event happens weekly I want to have
> one database record, not a separate record for each week of each
> month of each year, etc.
>
> Does anyone have any experience with this sort of thing that they
> would like to share? I am specifically interested in the best way
> to store the information about the repeat schedule in the database,
> and the most efficient way to query against this data to find which
> events will fall on any given day.
Assuming that you're using SQL Server, you could add a column in the events
table to flag how often the event occurs, then in your sql query retrieve
all those for the current month or day, plus all recurring events, i.e.
SELECT * FROM events
WHERE eventdate BETWEEN ( #startdate# and #enddate# )
OR eventrecurring IS NOT NULL
Your cfml code would then cycle through all the events in the query to
determine if a given event should be displayed in the cell for a given day.
<cftry>
<cfif myquery.eventdate eq currentdate>
<cfthrow message="current date">
<cfelseif len(myquery.eventrecurring)>
<cfswitch expression="#myquery.eventrecurring#">
<cfcase value="weekly">
<cfif not datediff(myquery.eventdate,currentdate) mod
7>
<cfthrow message="weekly">
</cfif>
</cfcase>
</cfswitch>
</cfif>
<cfcatch>... display the event ... </cfcatch>
</cftry>
Of course... this is an awful lot of cfml ...
I'm sure there's got to be a good way to do this with SQL ( I wonder if MS
SQL Server's task scheduler does something like this internally or just uses
the Windows task scheduler ) ... But I'm thinking about lookup tables, outer
joins and case statements and I'm just having a tough time seeing it in my
head...
Just pray they don't ask you for the ability to schedule events that occur
"every third thursday of the month". :)
hmm... actually... that gives me another idea... You could use a stored
procedure to drop the recurring events in a temp table using
dateadd(mm,x,eventdate) to add a specified number of months and then union
the temp table to the events table to retrieve the remaining events which
occur on a single date within the month... Your cfml code to move from month
to month would have to use a relative number of months or calculate the
number of months difference for the stored procedure.
hmmm...
If you create a table called "weekly" with a "days" column with 4 entries of
0,7,14 and 21 you might be able to use something like this:
<cfquery ...>
SELECT CASE eventrecurring
WHEN 'monthly' THEN DateAdd(mm,#addmonths#,eventdate)
WHEN 'weekly' THEN DateAdd(dd,weekly.days,DateAdd(mm,#addmonths#,eventdate))
ELSE eventrecurring END AS eventdate
FROM events
LEFT JOIN weekly ON ( events.eventrecurring = 'weekly' )
WHERE eventdate BETWEEN ( #startdate# AND #enddate# )
OR ( eventdate < #startdate# AND eventrecurring IS NOT NULL )
</cfquery>
You might run into problems with the first weekly event needing to be on the
first week of the month in order to prevent gaps at the beginning of
following months... ahh well... dates have always frustrated me in SQL
Server...
hth,
Isaac Dealey
Certified Advanced ColdFusion Developer
www.turnkey.to
954-776-0046
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
