Events calendar system - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Events calendar system

Author

Topic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-04-25 : 09:03:46

Hello everybody

I'm developing an events calendar system, which allows 'events' to be created.
Each event has a starting date and time, and a an ending date and time.

So in the database, this data can be captured with two fields, as follows.


StartDatetime    DATETIME,
EndDatetime      DATETIME

What I also need to do is allow to re-occuring events, so the table also has the following fields


RecurrenceRepeatType    TINYINT,
RecurrenceEndDate       DATETIME,
RecurrenceIsNoEndDate   BIT

The idea here is RecurrenceRepeatType specifies how often an event will re-occur. 0: Never, 1: Weekly, 2: Fortnightly: 3: Tri-weekly, 4:Quad-weekly, 5:Monthly.
RecurrenceEndDate specifies the date when the event stops re-occuring.
Finally RecurrenceIsNoEndDate is a flag, which sets if the event recurrence actually has an ending date. If it doesn't, it will re-occur forever.

So that's how the database stands currently.
When I designed it, it seems to make sense. However now what I need to do is write a query that will return any events that occured between two dates.
It's easy to pickup normal events between two dates, but I'm un-sure of how to factor in re-occuring events into the result set.
I don't know if it's just because it's Monday, but I can't think of a good way to do this.

I'm hoping that one of the resident SQL gurus could shed some light on this problem!

Thanks

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-25 : 11:15:16

Show us your table structure.

Jim
Users <> Logic

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-25 : 11:22:48

AND....can you show us some sample data, and what you're looking to get?

Brett

8-)

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-04-26 : 04:44:20

My mistake, I should have included this from the start.


CREATE TABLE [dbo].[Events] (
	[EventID] [int] IDENTITY (1, 1) NOT NULL ,
	[EventTitle] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
	[EventDescription] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL ,
	[StartDatetime] [datetime] NOT NULL ,
	[EndDatetime] [datetime] NOT NULL ,
	[RecurrenceRepeatType] [tinyint] NOT NULL ,
	[RecurrenceEndDate] [datetime] NULL ,
	[RecurrenceIsNoEndDate] [bit] NOT NULL ,
	[IsExternalSystemEvent] [bit] NOT NULL ,
	[ExternalDataID] [int] NOT NULL ,
	[ExternalSystemID] [int] NOT NULL ,
	[LocationID] [int] NOT NULL ,
	[DateAdded] [datetime] NOT NULL ,
	[IsLive] [bit] NOT NULL

There isn't currently any data in the table, as it's still in the development stages. So if a different data model is needed, it's not too late to change.

Thanks

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-04-27 : 04:49:20

No ideas?

What I'm looking to get?
I need to do is write a query that will return any events that occured between two dates.
It's easy to pickup normal events between two dates, but I'm un-sure of how to factor in re-occuring events into the result set.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-27 : 09:45:49

If this was my project I would use the Recurrence conditions to create another entry within your active working range (say 1 year) then keep it up with a weekly maint job.

This way you get them all with the original querry and the relationship stays the same.

Jim
Users <> Logic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-04-28 : 06:18:51

I was considering this, but the problem is how would events with no recurrence end date work?

The only two logical choices are to artifically set an end date (say 2050) for re-occuring events with no end date. The other is to remove the 'no end date' option, and to force the user to always specify an end date for re-occuring events.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-04-28 : 07:34:28

Set the weekly Maintenance job to only go out say a year or two. That way on the anniversary it will generate the next one. So it does not matter if it does not have an end date.

Jim
Users <> Logic

MaverickUK
Yak Posting Veteran

89 Posts

Posted - 2005-04-28 : 10:02:40

Yes, that's certainly an option.
I had just hoped, one of the wise DBAs here would have a more elegent solution, I guess not :/

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-04-28 : 13:54:39

Use 2 entities:
[Events], which holds the schedule, description etc.. of each event.
[EventInstantiation], which contains the actual instanciations of an event.
This entity contains the time&result for all the events that have occurred,
and also the scheduled time for events yet to occur.
[EventInstantiation]
(
EventID INT REFERENCES Events(EventID)
,InstantiationDate DATETIME
,Result/Errorcode/ ... data relevant to a particular eventinstantiation
,PRIMARY KEY(EventID,InstantiationDate)
)

This table would have to be maintained by a trigger on the [Events] table,
or by a scheduled job.

?

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources