Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nmg196
Yak Posting Veteran
70 Posts |
 Posted - 2002-09-18 : 18:26:25
|
| Hi,I have calendar application which will need to support recurring events (as with the windows Scheduler).An event will probably have just a start date, end date and an optional pattern for repetion (eg, birthdays recur every year, meetings might recur on the first monday of every month, reports might be due on the 7th of every month etc etc).I can't think of any nice way of putting this information in a database. It's easy enough to store annually recurring events, but as soon as you get things like "1st Monday" it starts to get difficult. Especially when it comes to queries. I will need to query the database to get any events between two dates (including events that started before this period, but end during the period). Specifically, I'll probably have to support the folling scenarios:1. Events which span time. ie, holidays, which may span several days). 2. Events which recur:- Every year- Every month (eg always on the 17th)- On the (first|second...last) <Monday|Tuesday...> of every month.Does anyone have any ideas about how I should go about storing this data or querying it? The only ways I've thought of so far are very messy and would require arrays to be built at application level as I can't think of a way to get the relavent data out at SQL Server level.Any thoughts would be appreciated.Thanks,Nick... |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-09-18 : 20:04:17
|
I would recommend having:A column that stores reocurrence type:1. Daily2. Weekly3. MonthlyA column that stores frequency type:1. Once2. ReocurringA column that stores frequency ie: 1 hours, 1 minutesA column that stores starting date for the frequency and one for end dateA duration column with start date and another with end date where null could mean run forever.Then write some store procedures that test for all the possible types of events and compare to see if they are supposed to kick off.The one problem I see is the granularity of your scheduled time vs your detected time. That is the time it will take to run these stored procedures might cause problems if you have something that is supposed to happen very frequently.A possible solution might be to retrieve a set of tasks ahead of time and schedule them in the windows scheduler or as a job.Take a look at the windows or sql server scheduler. |
 |
|
|
|
|
|
|
|
Storing recurring events nicely seems impossible..