## 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 used 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.

Nth day of every n months | ||

Days | Day n of the month | |

Interval | Of every n months value | |

IntervalFlag | Zero | |

Nth 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 nth 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 nth 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 nth 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. lets say we want the 2nd 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 2nd 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 1st 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.See full details: http://www.codeproject.com/KB/database/sqlscheduleselector.aspx

## No comments:

Post a Comment