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 DATETIMESET @@RangeStart = CAST('2007-03-31' AS DATETIME)DECLARE @@RangeEnd as DATETIMESET @@RangeEnd = CAST('2007-04-05' AS DATETIME)-- Will list all appointments (recurring or not) in the given rangeSELECT a.ID, a.Subject, 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 oWHERE -- 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:
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
Iana Tsolova is Product Manager at Telerik’s DevTools division. She joined the company back in the beginning of 2008 as a Support Officer and has since occupied various positions at Telerik, including Senior Support Officer, Team Lead at one of the ASP.NET AJAX teams and Technical Support Director. Iana’s main interests are web development, reading articles related to geography, wild nature and latest renewable energy technologies.