## 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
)
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,
@CHECKDATE datetime,
@INTERVAL int
)
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,
@WEEKDAY int,
@INSTANCE int
)
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 `UNION`

s to combine each result set into one recordset.

CREATE PROCEDURE [dbo].[GetScheduledAppointments]
(
@CHECKDATE datetime
)
AS
SET NOCOUNT ON
BEGIN
SELECT * FROM Schedule WHERE StartDate=@CHECKDATE AND Frequency=1
UNION
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
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
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)