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 intervalIntervalFlag
(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.
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,
@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 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 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)