UPDATED: Rebuilt project with the 2011 Q2 version of RadScheduler
You might have noticed that RadScheduler uses very cryptic strings to describe repeating appointments. We have decided not to reinvent the wheel and in fact this format is closely based on the iCalendar (RFC2445) standard. Here is an example:
This rule says: "The event starts at midnight on 18th of February 2008, lasts one day and repeats each year, for 3 years, on the second to last Monday of February". Now say this three times fast.
As much as standards-based those strings are, they are useless if you want to process your appointments on the database server itself. There are many reasons for you to want to do this. You might want to check for conflicting appointments. Or you might need to prepare some statistics for a report.
From version 2005 and up, SQL Server supports Managed Stored Procedures and User-Defined Functions (UDF). This exciting feature lets you plug in your own managed code into SQL Server and use it inside your queries. This is exactly what we need.
The solution we are presenting comes in the form of a managed UDF. This function uses the same recurrence engine as RadScheduler to do the heavy lifting required to evaluate the rules.
The attached file contains a setup script that enables CLR integration, registers the two assemblies (recurrence engine, UDF) and creates the function itself. Full source code of the UDF is included. Be sure to read the section on CLR integration on MSDN. There are good reasons that it is not enabled by default.
Let's see how all this comes together. A common scenario is to request all appointments in a given time range. Here is how we can do this with our function:
DECLARE @@RangeStart as DATETIME
SET @@RangeStart = CAST('2007-03-31' AS DATETIME)
DECLARE @@RangeEnd as DATETIME
SET @@RangeEnd = CAST('2007-04-05' AS DATETIME)
-- Will list all appointments (recurring or not) in the given range
ISNULL(o.StartDate, a.Start) as Start,
ISNULL(o.EndDate, a.[End]) as [End]
FROM Appointments a
OUTER APPLY dbo.ExpandRecurrence(a.RecurrenceRule, @@RangeStart, @@RangeEnd) AS o
WHERE -- Include non-recurring appointments in the range
(a.RecurrenceRule IS NULL AND a.Start < @@RangeEnd AND a.[End] > @@RangeStart) OR
-- And recurring appointments in range.
(a.RecurrenceRule IS NOT NULL AND o.StartDate < @@RangeEnd AND o.EndDate > @@RangeStart)
ORDER BY Start
This yields the following results:
||Meeting with Alex
You can see how "Technical meeting" has been expanded into several appointments, according to it's recurrence rule.
* Applicable to SQL Server 2005 and up