Telerik blogs

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:

DTSTART:20080218T000000Z
DTEND:20080219T000000Z
RRULE:FREQ=MONTHLY;INTERVAL=12;BYDAY=-2MO;COUNT=3;

 

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
SELECT 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 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:

ID Subject Start End
24 Meeting with Alex 2007-03-31 06:00:00 2007-03-31 07:00:00
30 Test Appointment 2007-03-31 07:30:00 2007-03-31 08:30:00
15 Technical meeting 2007-04-02 06:30:00 2007-04-02 07:30:00
15 Technical meeting 2007-04-03 06:30:00 2007-04-03 07:30:00
15 Technical meeting 2007-04-04 06:30:00 2007-04-04 07:30:00

 

You can see how "Technical meeting" has been expanded into several appointments, according to it's recurrence rule.

Additional resources:

Have fun!

 

* Applicable to SQL Server 2005 and up

 Attached:  ExpandRecurrence_UDF_20010903


Tsvetomir Tsonev
About the Author

Tsvetomir Tsonev

Tsvetomir is a developer on the Kendo UI team. He enjoys helping others as much as creating software. In his spare time, Tsetso loves reading science fiction and photographing nature.

Comments

Comments are disabled in preview mode.