SQL Server Retrieving Recurring Appointments By Date

Question

I'm working on a system to store appointments and recurring appointments. My schema looks like this

Appointment
-----------
ID
Start
End
Title
RecurringType
RecurringEnd

RecurringTypes
---------------
Id
Name

I've keeped the Recurring Types simple and only support

Week Days,
Weekly,
4 Weekly,
52 Weekly

If RecurringType is null then that appointment does not recur, RecurringEnd is also nullable and if its null but RecurringType is a value then it will recur indefinatly. I'm trying to write a stored procedure to return all appointments and their dates for a given date range.

I've got the stored procedure working for non recurring meetings but am struggling to work out the best way to return the recurrences this is what I have so far

ALTER PROCEDURE GetAppointments
(
    @StartDate DATETIME,
    @EndDate DATETIME
)
AS

SELECT  
    appointment.id,
    appointment.title,
    appointment.recurringType,
    appointment.recurringEnd,
    appointment.start,
    appointment.[end]
FROM
    mrm_booking
WHERE
    (
        Start >= @StartDate AND
        [End] <= @EndDate
    ) 

I now need to add in the where clauses to also pick up the recurrences and alter what is returned in the select to return the Start and End Dates for normal meetings and the calculated start/end dates for the recurrences.

Any pointers on the best way to handle this would be great. I'm using SQL Server 2005

Answer 1

you need to store the recurring dates as each individual row in the schedule. that is, you need to expand the recurring dates on the initial save. Without doing this it is impossible to (or extremely difficult) to expand them on the fly when you need to see them, check for conflicts, etc. this will make all appointments work the same, since they will all actually have a row in the table to load, etc. I would suggest that when a user specifies their recurring date, you make them pick an actual number of recurring occurrences. When you go to save that recurring appointment, expand them all out as individual rows in the table. You could use a FK to a parent appointment row and link them like a linked list:

Appointment
-----------
ID
Start
End
Title
RecurringParentID  FK to ID

sample data:

ID ....  RecurringParentID
1  ....  null
2  ....  1
3  ....  2
4  ....  3
5  ....  4

if in the middle of the recurring appointments schedule run, say ID=3, they decide to cancel them, you can follow the chain and delete the remaining ID=3,4,5.

as for expanding the dates, you could use a CTE, numbers table, while loop, etc. if you need help doing that, just ask. the key is to save them as regular rows in the table so you don't need to expand them on the fly every time you need to display or evaluate them.

Answer 2

I ended up doing this by creating a temp table of everyday between the start and end date along with their respective day of the week. I limited the recurrence intervals to weekdays and a set amount of weeks and added where clauses like this

        --Check Week Days Reoccurrence
        (
            mrm_booking.repeat_type_id = 1 AND              
            #ValidWeeklyDayOfWeeks.dow IN (1,2,3,4,5)
        ) OR
        --Check Weekly Reoccurrence
        (
            mrm_booking.repeat_type_id = 2 AND
            DATEPART(WEEKDAY, mrm_booking.start_date) = #ValidWeeklyDayOfWeeks.dow
        ) OR
        --Check 4 Weekly Reoccurences
        (
            mrm_booking.repeat_type_id = 3 AND
            DATEDIFF(d,#ValidWeeklyDayOfWeeks.[Date],mrm_booking.start_date) % (7*4) = 0
        ) OR
        --Check 52 Weekly Reoccurences
        (
            mrm_booking.repeat_type_id = 4 AND
            DATEDIFF(d,#ValidWeeklyDayOfWeeks.[Date],mrm_booking.start_date) % (7*52) = 0
        )

In case your interested I built up a table of the days between the start and end date using this

INSERT INTO #ValidWeeklyDayOfWeeks
--Get Valid Reoccurence Dates For Week Day Reoccurences
SELECT
    DATEADD(d, offset - 1, @StartDate) AS [Date],
    DATEPART(WEEKDAY,DATEADD(d, offset - 1, @StartDate)) AS Dow
FROM
    (
        SELECT ROW_NUMBER() OVER(ORDER BY s1.id) AS offset
        FROM syscolumns s1, syscolumns s2
    ) a WHERE offset <= DATEDIFF(d, @StartDate, DATEADD(d,1,@EndDate)) 

Its not very elegant and probably very specific to my needs but it does the job I needed it to do.