S
Siv
Guest
Hi,
I have an application that uses an SQL database to store appointments in a similar fashion to the way Outlook does. For various reasons I cannot use Outlook or an Outlook type 3rd party control as this application does not behave like a normal Calendar in the way it's implemented.
At the moment I can create normal Calendar entries and this works fine, each calendar entry has a record in my calendar table and I can run an SQL query against a date range when I am displaying say a day a week or a month view.
What I want to do is implement recurring calendar entries using the same sort of thinking as the way it's done in Outlook. I have been thinking it through and come to the conclusion that I need a separate table that holds recurring appointments and I have created a table structure like this:
ActionCalendarRecurringItemsID int 'Primary key
fkActionCalendarID int 'Link to the main Calendar entries
RecurrenceType int '1=Daily, 2=Weekly, 3=Monthly, 4=Yearly
RecurrenceRepeatNumber int 'If the RecurrenceType is 2 (weekly) and this number is 3 then the recurrence is 3 weekly.
RecurrenceSpecificDateDay int 'If the recurrenceType is 2 (weekly) and this field is set to 3 then it would represent weekly every Wednesday.
RecurrenceSpecificDateMonth int 'If RecurrenceRepeatNumber is 4 (Yearly) and SpecificDateDay=22 and this field is 4 then we repeat every year on the 22nd April.
RecurrenceSpecificDateYear int 'If RepeatNumber is 4 (Yearly) and SpecificDay is 12, Specific Month=6 and this field is 2014 then this appointments recurs on 12/6/2014.
RecurrenceSpecificDay int 'This is the day of the week (1=Monday, 2=Tuesday ... Sunday= 7 ). Thus if Type= 2 (weekly) RepeatNumber=2 and this field is 3 then we repeat every two weeks on a Wednesday.
WeekdayYN bit 'If 1=Only Weekdays, if 0= Weekend days
FirstYN bit '1=The first day of a period, 0=Not the first day.
LastYN bit '1=Last day of period selected, 0=Not Last Day of period Selected.
RecurrenceStartDate smalldatetime 'Start of recurrence
RecurrenceEndDate smalldatetime 'End of recurrence
RecurrenceNumberOfRepeats int 'If you set type=4 (yearly) and this field as 5 then this will recur over the next 5 years.
RecordIsDeletedYN bit 'Indicates the user has deleted this record.
DateCreated smalldatetime 'Date Record first created.
CreatedBy varchar(20) 'User Who Created The Record
DateModified smalldatetime 'Date record last modified
ModifiedBy varchar(20) 'User who last modified this record.
My issue is going to be how do I use these records to efficiently display my daily weekly and monthly views of my calendar without having to look up all the recurring records and run through working out whether any of the recurring items fall within my displayed calendar view.
I have been toying with ideas like storing say the first 31 ocurrences of the recurring items as comma separated values in a string field that I can quickly query to see if any of these dates falls in my calendar view range. This would have to be maintained on a daily basis as some sort of maintenance task that the program updates these dates as the date changes so that they are always holding the next 31 items going forward.
Has anyone else had to deal with this issue and if so how did you crack it, indeed how did Microsoft crack it in Outlook?
I had a look for algorithms that do this and didn't find anything that looked like what I am searching for??
Siv
Graham Sivill - Martley, Worcester. UK
Continue reading...
I have an application that uses an SQL database to store appointments in a similar fashion to the way Outlook does. For various reasons I cannot use Outlook or an Outlook type 3rd party control as this application does not behave like a normal Calendar in the way it's implemented.
At the moment I can create normal Calendar entries and this works fine, each calendar entry has a record in my calendar table and I can run an SQL query against a date range when I am displaying say a day a week or a month view.
What I want to do is implement recurring calendar entries using the same sort of thinking as the way it's done in Outlook. I have been thinking it through and come to the conclusion that I need a separate table that holds recurring appointments and I have created a table structure like this:
ActionCalendarRecurringItemsID int 'Primary key
fkActionCalendarID int 'Link to the main Calendar entries
RecurrenceType int '1=Daily, 2=Weekly, 3=Monthly, 4=Yearly
RecurrenceRepeatNumber int 'If the RecurrenceType is 2 (weekly) and this number is 3 then the recurrence is 3 weekly.
RecurrenceSpecificDateDay int 'If the recurrenceType is 2 (weekly) and this field is set to 3 then it would represent weekly every Wednesday.
RecurrenceSpecificDateMonth int 'If RecurrenceRepeatNumber is 4 (Yearly) and SpecificDateDay=22 and this field is 4 then we repeat every year on the 22nd April.
RecurrenceSpecificDateYear int 'If RepeatNumber is 4 (Yearly) and SpecificDay is 12, Specific Month=6 and this field is 2014 then this appointments recurs on 12/6/2014.
RecurrenceSpecificDay int 'This is the day of the week (1=Monday, 2=Tuesday ... Sunday= 7 ). Thus if Type= 2 (weekly) RepeatNumber=2 and this field is 3 then we repeat every two weeks on a Wednesday.
WeekdayYN bit 'If 1=Only Weekdays, if 0= Weekend days
FirstYN bit '1=The first day of a period, 0=Not the first day.
LastYN bit '1=Last day of period selected, 0=Not Last Day of period Selected.
RecurrenceStartDate smalldatetime 'Start of recurrence
RecurrenceEndDate smalldatetime 'End of recurrence
RecurrenceNumberOfRepeats int 'If you set type=4 (yearly) and this field as 5 then this will recur over the next 5 years.
RecordIsDeletedYN bit 'Indicates the user has deleted this record.
DateCreated smalldatetime 'Date Record first created.
CreatedBy varchar(20) 'User Who Created The Record
DateModified smalldatetime 'Date record last modified
ModifiedBy varchar(20) 'User who last modified this record.
My issue is going to be how do I use these records to efficiently display my daily weekly and monthly views of my calendar without having to look up all the recurring records and run through working out whether any of the recurring items fall within my displayed calendar view.
I have been toying with ideas like storing say the first 31 ocurrences of the recurring items as comma separated values in a string field that I can quickly query to see if any of these dates falls in my calendar view range. This would have to be maintained on a daily basis as some sort of maintenance task that the program updates these dates as the date changes so that they are always holding the next 31 items going forward.
Has anyone else had to deal with this issue and if so how did you crack it, indeed how did Microsoft crack it in Outlook?
I had a look for algorithms that do this and didn't find anything that looked like what I am searching for??
Siv
Graham Sivill - Martley, Worcester. UK
Continue reading...