Introduction

I wanted to store appointments in a database and be able to retrieve records that fall on a specified date without storing all occurrences of the appointments until some arbitrary future date. The plan was to create the “master” appointment records which describe the schedule and use date logic to determine which appointments fall on any given date.

This code supports the following schedules:

One-off on a specified date
Daily – on specified week days or every n days
Weekly - every n weeks on specified week days
Monthly – day x of every n months, or the first/second/third/fourth/last week day of every n months

To help visualise the scene, here’s a screenshot of the demo scheduler. The source code is attached.

Using the Code

Run the CreateSchedulerDB.sql script in the SQL folder within the project source to create the Scheduler database. The Schedule data table contains the following fields:

Startdate (datetime) = the date the schedule starts i.e. the first occurrence (or in the case of a one-off, the one-off date)
Enddate (datetime) = the date the schedule finishes (null if infinite)
Frequency (tinyint) = frequency type (1=one-off, 2=daily, 3=weekly, 4=monthly)
Days (tinyint) = 7 bits representing days of the week where Sun=1, Mon=2, Tues=4, Wed=8, Thu=16, Fri=32, Sat=64 so an appointment on every day=127. Note that Sunday is used as the first day of the week.
Interval (smallint) = the size of the interval
IntervalFlag (int) = an interval flag used to branch logic checks for daily or monthly where there are multiple options, e.g. day 1 of every 2 months, or the first Monday of every 2 months. For appointments like the first Monday of every 3 months, this field stores the 3.

The stored procedure GetScheduleForDate takes a date parameter and returns a recordset containing only the appointments that fall on the specified date.

One-offs (Frequency Type 1)

One-offs are the simplest to implement. Select any records where the startdate equals the check date. In this case EndDate is irrelevant and can be ignored.

SELECT * FROM Schedule WHERE StartDate=@CHECKDATE AND Frequency=1

Daily (Frequency Type 2)

Daily introduces the first scalar-valued User Defined Function called DayValueFromBits. Because I store the days we need as a bitmask where Sunday=1, Monday=2, etc., this determines the week day from the supplied date so it can be compared to the days in the appointment record.

CREATE FUNCTION [dbo].[DayValueFromBits]
(
      @CHECKDATE datetime --the date we’re checking
)
RETURNS smallint
AS
BEGIN
    DECLARE @DAY smallint
    SET @DAY = DATEPART(dw,@CHECKDATE)-1
    RETURN POWER(2,@DAY)
END

@DAY stores the weekday from the check date. Because any number raised to the power of zero is 1 (i.e. Sunday), we must subtract 1 from the day so it results in the correct bit. Saturday (7) subtract 1=6 raised to the power of 2 = 64.

That’s it for the daily selection. The SQL looks like this:

SELECT * FROM Schedule WHERE (((dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 _
	AND IntervalFlag=0) 
Or (datediff(dd,@CHECKDATE,StartDate)%NULLIF(Interval,0) = 0 AND IntervalFlag = 1) ) 
AND StartDate <= @CHECKDATE AND _
(EndDate Is Null OR EndDate > @CHECKDATE) AND Frequency=2

First we call the DayValueFromBits UDF and do a logical AND with the Days data field. If the result is greater than zero, we have a matching date.

IntervalFlag is used here to denote whether the appointment is every n days (IntervalFlag=1) or every Week day (IntervalFlag=0). DayValueFromBits deals with the weekdays.

Now let’s look at the every n days scenario. Here we get the number of days difference between the start date and @CHECKDATE using datediff, and MOD this value with the Interval (i.e. the %NULLIF bit) to get the remainder. If this is zero, then we have a matching date. We use NULLIF to filter out occasions where the interval is zero which would return a false positive, i.e., if the Interval matches zero then return a null value which causes the modulus to fail.

Weekly (Frequency Type 3)

Here we use the next UDF WeeklyDays. This calculates the difference in weeks between the start date and @CHECKDATE and then MODS this value with the interval to determine whether the date is valid. No remainder results in a positive match. The SQL for the WeeklyDays function is shown below:

CREATE FUNCTION [dbo].[WeeklyDays]
(
    @STARTDATE datetime, --the start date of the appointment
    @CHECKDATE datetime, --the date we’re checking
    @INTERVAL int --the interval
)
RETURNS bit
AS
BEGIN
    DECLARE @WDIFF int, 
    @RESULT bit
    SET @WDIFF = DATEDIFF(ww,@STARTDATE,@CHECKDATE)
    SET @RESULT = 0
    IF @WDIFF%@INTERVAL = 0
        SET @RESULT = 1
    RETURN @RESULT
END

This is all that is required for weekly selection. The SQL query is shown below:

SELECT * FROM Schedule WHERE (dbo.WeeklyDays(StartDate,@CHECKDATE,Interval)=true 
AND (dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 AND StartDate <= @CHECKDATE 
AND (EndDate Is Null OR EndDate > @CHECKDATE)) AND Frequency = 3

WeeklyDays takes care of the correct week and DayValueFromBits takes care of the week days as before.

Monthly (Frequency Type 4)

Monthly uses a number of UDFs. The monthly calculation is more complex because we want to calculate first, second, third, fourth or last occurrence within the month.

The table below shows how the schedule fields are stored for monthly appointments because the values differ compared to the previous appointment types. Most notable is that rather than a bit mask, the Day field now holds the day of month or week day value.

N^th day of every n months
`Days`	Day n of the month
`Interval`	Of every n months value
`IntervalFlag`	Zero
N^th week day of every n months
`Days`	1=First, 2=second...5=Last
`Interval`	Week day where Sunday=1
`IntervalFlag`	Of every n months value

The MonthlyDays UDF performs a similar function to WeeklyDays but checks the difference in months.

If we are checking the n^th day of every n months (IntervalFlag=0), we add the difference in months back on to the start date to ensure the dates match. This ensures the month hasn’t rolled over due to differences in month lengths. As before, a zero remainder means a positive match.

If we are checking the n^th occurrence of a weekday in every n months (IntervalFlag= every n months value), there is no need to add the date back, but note we use IntervalFlag because this is holding the n months value this time, not Interval.

CREATE FUNCTION [dbo].[MonthlyDays] 
(
    @STARTDATE datetime,
    @CHECKDATE datetime,
    @INTERVAL int,
    @INTERVALFLAG int
)
RETURNS bit
AS
BEGIN
    DECLARE @MDIFF int,
           @NDIFF datetime,
           @RESULT bit
    SET @RESULT = 0
 
    IF @INTERVALFLAG=0
    BEGIN
        SET @MDIFF = DATEDIFF(mm,@STARTDATE,@CHECKDATE)
        IF @MDIFF%@INTERVAL=0
        BEGIN
            SET @NDIFF = DATEADD(mm,@MDIFF,@STARTDATE)
            IF @NDIFF=@CHECKDATE
                BEGIN
                    SET @RESULT = 1
                END
            END
    END
    ELSE
    BEGIN
        SET @MDIFF = DATEDIFF(mm,@STARTDATE,@CHECKDATE)
        IF @MDIFF%@INTERVALFLAG=0
        BEGIN
            SET @RESULT = 1
        END
    END
    RETURN @RESULT
END

The next UDF is MonthlyDayOccurrence. This calculates the n^th occurrence of a weekday within the month and returns the date of that occurrence. We can then check whether it matches our check date. The SQL is shown below:

CREATE FUNCTION [dbo].[MonthDayOccurrence]
(
    @CHECKDATE datetime, --the date we’re checking (day is ignored)
    @WEEKDAY int, --the weekday to check for
    @INSTANCE int --the week day instance to find
)
RETURNS datetime
AS
BEGIN
    DECLARE @RESULT datetime
    DECLARE @DAY int
    SET @DAY = DATEPART(d,@CHECKDATE)
    IF @INSTANCE < 5
    BEGIN
        SET @RESULT = @CHECKDATE - @DAY + _
	(7 * @INSTANCE + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
    END
    ELSE
    BEGIN –-last occurrence in the month check
        SET @RESULT = @CHECKDATE - @DAY + _
	(7 * @INSTANCE + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
        IF DATEPART(m,@CHECKDATE) != DATEPART(m,@RESULT)
        BEGIN
            SET @RESULT = @CHECKDATE - @DAY + _
		(7 * 4 + 1 - dbo.WeekDay(@CHECKDATE - @DAY, @WEEKDAY))
        END
    END
    RETURN @RESULT
END

Firstly we get the day of the month from our check date using the datepart function. We need this to pass into the following algorithm that does the legwork for us:

{Date to check} – {Day of month} + ( 7 * {Instance} + 1 – {Day of week})

E.g. let's say we want the 2^nd Friday in January 2009 which just happens to have the date 09/01/2009. Using the above algorithm, we get (using a date to check of 09/01/2009):

Date to check = 09/01/2009
Day of month = 9
Instance = 2 (i.e. 2nd Friday)
Day of week = 6 (i.e. Friday based on Sunday being day 1)
09/01/2009 – 9 + (7 * 2 + 1 – 6) =>
09/01/2009 – 9 + 9 =>
09/01/2009

So our check date and the date calculated by MonthlyDayOccurrence match so we know this is the 2^nd Friday in January.

We’re not quite out of the woods yet since what if we want the last occurrence (in which case we pass in an instance value of 5)? Sometimes the month has a fifth occurrence and other times not dependent on which day of the week the 1^st of the month falls. To catch this, we perform the same check as above but then check that the month still matches that of the check date. If not, then we’ve gone too far so we return the fourth occurrence as the “last” (in which case fourth and last are the same).

The final UDF called WeekDay is an implementation of the standard Excel style WeekDay function that returns the day of the week number based on a supplied first day of the week. This is used by the MonthlyDayOccurrence algorithm discussed above.

CREATE FUNCTION [dbo].[WeekDay]
(
    @DATE datetime,
    @FIRSTDAYOFWEEK int
)
RETURNS int
AS
BEGIN
    DECLARE @OFFSET int,
    @DAY int
    SET @OFFSET = @FIRSTDAYOFWEEK - 1
    SET @DAY = DATEPART(dw,@DATE)
    SET @DAY = @DAY - @OFFSET
    IF @DAY < 1
    BEGIN
        SET @DAY = @DAY + 7
    END
    RETURN @DAY
END

One final point is that for monthly appointments to work using the logic discussed above, the start date value needs to be aligned to the first instance of the appointment when the record is saved. Therefore there is a client-side implementation of the WeekDay function and the MonthlyDayOccurrence procedures that are called when the record is saved. This moves the start date forward to the next occurrence and then saves the record with the adjusted date. These are in the GlobalFunctions static class in the demo project.

This is everything needed for the monthly calculation. The SQL is shown below:

SELECT * FROM Schedule WHERE (((dbo.MonthlyDays_
	(StartDate,@CHECKDATE,Interval,IntervalFlag))='true' 
AND datepart(d,@CHECKDATE) = Days AND IntervalFlag = 0) 
Or (@CHECKDATE = dbo.MonthDayOccurrence(@CHECKDATE,Days,Interval) AND 
dbo.MonthlyDays(StartDate,@CHECKDATE,Interval,IntervalFlag)='true' AND 
IntervalFlag > 0)) AND Frequency = 4 AND StartDate <= @CHECKDATE  AND 
(EndDate >= @CHECKDATE OR EndDate = '1900/01/01 00:00:00')

We’ve used our IntervalFlag again to denote whether we’re dealing with day n of every x months (IntervalFlag=0) or the first/second etc day of every n months (IntervalFlag = 1).

All that remains now is to use UNIONs to combine each result set into one recordset.

CREATE PROCEDURE [dbo].[GetScheduledAppointments]
(
    @CHECKDATE datetime
)
AS
SET NOCOUNT ON
BEGIN
--one off
    SELECT * FROM Schedule WHERE StartDate=@CHECKDATE AND Frequency=1
UNION
--daily
    SELECT * FROM Schedule WHERE (((dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 
AND IntervalFlag=0) Or (datediff(dd,@CHECKDATE,StartDate)%NULLIF(Interval,0) = 0 
AND IntervalFlag = 1) ) AND StartDate <= @CHECKDATE AND (EndDate Is Null 
OR EndDate > @CHECKDATE) AND Frequency=2
UNION
--weekly
    SELECT * FROM Schedule WHERE (dbo.WeeklyDays(StartDate,@CHECKDATE,Interval)=true 
AND (dbo.DayValueFromBits(@CHECKDATE) & Days) > 0 AND StartDate <= @CHECKDATE 
AND (EndDate Is Null OR EndDate > @CHECKDATE)) AND Frequency = 3
UNION
--monthly
  SELECT * FROM Schedule WHERE (((dbo.MonthlyDays_
	(StartDate,@CHECKDATE,Interval,IntervalFlag))='true' 
AND datepart(d,@CHECKDATE) = Days AND IntervalFlag = 0) 
Or (@CHECKDATE = dbo.MonthDayOccurrence(@CHECKDATE,Days,Interval) 
AND dbo.MonthlyDays(StartDate,@CHECKDATE,Interval,IntervalFlag)='true' 
AND IntervalFlag > 0)) AND Frequency = 4 AND StartDate <= @CHECKDATE  
AND (EndDate >= @CHECKDATE OR EndDate = '1900/01/01 00:00:00')
END

You can call GetScheduleForDate passing the date to check and the proc will return a recordset of all matching appointments that fall on the date. All the record selection processing is done at the server end where it belongs, you aren’t passing irrelevant data records over the wire, the SQL is portable to other projects and you can tweak the selection without having to recompile your programs.

Points to Note

I’m based in the UK hence my clients use dates in DD/MM/YYYY format. I haven’t tried other date formats.

In the demo app, don't forget to edit the app.config file to change the database instance name as appropriate.

Future Improvements

It would be preferable to perform the date alignment on the SQL server to remove the onus on the coder to do this client-side. I'll post an update once I have a solution.

History

14/01/2009 - Initial article posted
20/11/2009 - Added sample project and database script, together with omissions from original version (thanks to those who pointed this out)

How to Use SQL Server to Select Records for a Scheduled Date