The solution I have come up with is that I have an event table that has five fields defining the recurrence of the event, as defined below. I then also have a schedule table which I populate with the actual occurrence of the events. I do require an end date, and even when they specify something to go out to a couple years out, it is a monthly type event which does not create that many entries into the schedule table.
So, the event is stored in an event table, with a startDateTime and an endDateTime that describe the entire duration of the event if there is no recurrence. These two datetime fields also define the overall start and end of the event if it is a recurring event. In that same event table, we have the five fields defining recurrence, as laid out below.
The Schedule table stores individual occurrences of each event. So it has an eventId, startDateTime, and endDateTime. This start and end refer only to each occurrence, not the overall span.
For querying for all the scheduled occurrences happening in a period of time, I just query the schedule table checking for any occurrences that match this condition:
select * from schedule where schedule.startDateTime < @queryPeriodEnd and schedule.endDateTime > @queryPeriodStart
This query gives me only the schedule entries that happen partially or wholly within my query period. For getting the event data, it's a simple matter of joining to the event table.
The interesting part is calculating something like the second thursday of the month. That happens in the actual code for figuring out all the scheduled occurrences for a given event. I am also enclosing my code for that below.
EVENT RECURRENCE FIELDS
recurs
0=no recurrence
1=daily
2=weekly
3=monthly
recurs_interval
this is how many of the periods between recurrences. If the event recurs every 5 days, recurs_interval will have a 5 and recurs will have 1. If the event recurs every 3 weeks, recurs_interval will have a 3 and recurs will have a 2.
recurs_day
If the user selected monthly type recurrence, on a given day of the month (ex: 10th or the 14th). This has that date. The value is 0 if the user did not select monthly or specific day of month recurrence. The value is 1 to 31 otherwise.
recurs_ordinal
if the user selected a monthly type recurrence, but an ordinal type of day (ex: first monday, second thursday, last friday). This will have that ordinal number. The value is 0 if the user did not select this type of recurrence.
1=first
2=second
3=third
4=fourth
5=last
recurs_weekdays
for weekly and monthly-ordinal recurrence this stores the weekdays where the recurrence happens. 1=Sunday
2=Monday
4=Tuesday
8=Wednesday
16=Thursday
32=Friday
64=Saturday
So, every 4 weeks on Saturday and Sunday would be
recurs=2, recurs_interval=4, recurs_weekdays=65 (64 + 1)
Similarly, Every three months on the first Friday of the month would be
recurs=3, recurs_interval=3, recurs_ordinal=1, recurs_weekdays=32
CODE
thisEvent.occurrences = new List<ScheduleInstance>();
DateTime currentDateTime = (DateTime) thisEvent.start;
DateTime currentEndTime;
BitArray WeekDayRecurrenceBits = new BitArray(new Byte[] {(Byte) thisEvent.recursWeekdays});
while (currentDateTime < thisEvent.end)
{
currentEndTime = new DateTime(currentDateTime.Year, currentDateTime.Month, currentDateTime.Day,
thisEvent.end.Value.Hour, thisEvent.end.Value.Minute, thisEvent.end.Value.Second);
switch (thisEvent.recurs)
{
case (RecurrenceTypeEnum.None):
AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
currentDateTime = (DateTime)thisEvent.end;
break;
case (RecurrenceTypeEnum.Daily):
AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
currentDateTime = currentDateTime.AddDays(thisEvent.recursInterval);
break;
case (RecurrenceTypeEnum.Weekly):
int indexIntoCurrentWeek = (int) currentDateTime.DayOfWeek;
while ((indexIntoCurrentWeek < 7) && (currentDateTime < thisEvent.end))
{
if (WeekDayRecurrenceBits[(int) currentDateTime.DayOfWeek])
{
AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
}
currentDateTime = currentDateTime.AddDays(1);
currentEndTime = currentEndTime.AddDays(1);
indexIntoCurrentWeek++;
}
currentDateTime = currentDateTime.AddDays(7 * (thisEvent.recursInterval - 1));
break;
case (RecurrenceTypeEnum.Monthly):
if (thisEvent.recursDay == 0)
{
DateTime FirstOfTheMonth = new DateTime(currentDateTime.Year, currentDateTime.Month, 1);
int daysToScheduleOccurrence = ((thisEvent.recursWeekdays - (int)FirstOfTheMonth.DayOfWeek + 7) % 7)
+ ((thisEvent.recursOrdinal - 1) * 7)
- currentDateTime.Day + 1;
if (daysToScheduleOccurrence >= 0)
{
currentDateTime = currentDateTime.AddDays(daysToScheduleOccurrence);
currentEndTime = currentEndTime.AddDays(daysToScheduleOccurrence);
if (currentDateTime < thisEvent.end)
{
AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
}
}
}
else
{
if (currentDateTime.Day <= thisEvent.recursDay && thisEvent.recursDay <= DateTime.DaysInMonth(currentDateTime.Year, currentDateTime.Month) )
{
currentDateTime = currentDateTime.AddDays(thisEvent.recursDay - currentDateTime.Day);
currentEndTime = currentEndTime.AddDays(thisEvent.recursDay - currentEndTime.Day);
AddOccurrenceToRooms(thisEvent, currentDateTime, currentEndTime);
}
}
currentDateTime = currentDateTime.AddDays((currentDateTime.Day - 1) * -1).AddMonths(thisEvent.recursInterval);
break;
default:
break;
}
}
