Reoccuring Scheduling Logic

Ok,
So let's assume that you are a software developer and have a database driven scheduling application. Since this is a database application that is to deliver millions of requests per day, and the exact start time isn't any more granular than the nearest hour, you don't want to perform these lookups in real time. Instead you decide to create a stored procedure that will run as a scheduled job. The plan is that a scheduled job will run a stored procedure that will toggle the availability of requested files or not depending upon if the request occurs during a valid date range. Records that are available will have a bit flag set to 1. Records that aren't will have a bit flag set to 0.
Testing that you're in a valid date range is certainly easy enough and if you are writing stored procedures at all, you should already know how to do this, but for any newbee reading this, the psudo-code for such a test will be Update Schedule where now is between the start date and end date. Ok, so what if there is a requirement for scheduling things between a date range and the client only wants to deliver this information every other week or less? You could write a routine that would inject multiple date ranges into the table. So instead of a date range that spanned a 90 day period, you could inject 12 one week date ranges. That is far from elegant. Perhaps we could come up with a simple algorithym to calculate if we are within a valid week within that valid date range. We can do so by bringing a little of our high school algebra out of the closet.
You remember Algebra, don't you? It was that stuff you swore you'd never find useful in the real world. Well, here's a real world example. For this example we're going to need three data points and three variables representing them. Snce our scheduler is concerned with weekly cycles, and integers are far easier to work with, we will be converting our dates into integers using built in database DateTime functions and our three data points will be our the week of our scheduled start date (SW); an integer representing our weekly cycle (WC) which is the number of weeks that elapse before our cycle resets, and the current time (CW).
What we need to determine is what is the number of weeks between the current time and our scheduled start date, and if that number of weeks works out to an even number. We can accomplish this two ways. The first is a little more verbose, but I like it because it is more self explanitory. That is, each step in the equation provides a piece to why this formula makes sense.
((CW-SW)/WC)*WC+SW=CW
(CW-SW) = the number of weeks from the Start Week and the Current week.
((CW-SW)/WC) = the number of weekly cycles that have elapsed.
*WC = The number of weeks in the cycle itself
((CW-SW)/WC)*WC+SW= when we compute this if the result equals Current week, we set our value to true.

Let's look at a simple example where our schedule started on Sept 7th, and runs every other week and the current day is Sept 27th. Converted into integers we have
CW= 39, SW=37, WC = 2

plugged into our formula ((CW-SW)/WC)*WC+SW=CW gives us
((39-37)/2)*2+37=39

simplifies to ((2)/2)*2+37=39
simplifies to (1)*2+37=39
simplifies to 22+37=39
simplifies to 39=39.
Since the left and right side are equal we set our record to active.

Now while the above is a workable and self evident formula, let's see how else this can be done.
Remembering our algebra again if instead of testing if our equation equals CW, we can modify our equation by subtracting CW from each side to see if the result equals 0. So
((CW-SW)/WC)*WC+SW=CW
is equivelant to
((CW-SW)/WC)*WC+SW-CW=CW-CW
is equivelant to
((CW-SW)/WC)*WC+SW-CW=0

if you have SQL Server Query Analyzer open paste in the following

Declare @CW int
Declare @SW int
Declare @WC int
Select @CW = datePart(week, Getdate()), @SW= datePart(week,'9/7/2008'), @WC = 3
if ((@CW-@SW)/@WC)*@WC+@SW-@CW = 0
Print 'Perform update: Number of weeks divides evenly into the weekly cycle!'
else
print 'Can''t perform update! Number of weeks doesn''t divide evenly into the weekly cycle!'
print 'The current week is ' + cast(@CW as nvarchar(2))
print 'The start week is ' + cast(@SW as nvarchar(2))

Now we have a working formula that while not as terse as possible get's the job done and vidicates our high school math teacher. However, if you look at the formula, you might realize there is a simple math function that can replace 4 steps in the calculation. The hint is in the print statements: 'divides evenly into the weekly cycle.'
Modulus can take the place of the portion of this formula in red:
((CW-SW)/WC)*WC+SW-CW=0
as in (CW-SW)%WC= 0.

So paste the code below into Query Analyzer:

Declare @CW int
Declare @SW int
Declare @WC int
Select @CW = datePart(week, Getdate()), @SW= datePart(week,'9/7/2008'), @WC = 1
if ((@CW-@SW)%@WC) = 0
Print 'Perform update: Number of weeks divides evenly into the weekly cycle!'
else
print 'Can''t perform update! Number of weeks doesn''t divide evenly into the weekly cycle!'
print 'The current week is ' + cast(@CW as nvarchar(2))
print 'The start week is ' + cast(@SW as nvarchar(2))

So there you have it. You can now calculate schedules that occur in frequencies less than every week. Of course, the same logic could be used to calculate monthly or other cycles.

Here is the entire thing in a function:


ALTER FUNCTION [dbo].[fn_Check_Schedule]
(@StartDate DATETIME,
@EndDate DATETIME,
@DaysOfWeek TINYINT,
@CurrDate DATETIME,
@WeeklyCycle int
)
RETURNS BIT AS
BEGIN
DECLARE @returnval bit
Declare @ValidWeek int
set @returnval = 0
Set @ValidWeek = 0
if @WeeklyCycle > 1
BEGIN
Select @ValidWeek = ((Datepart(week, @CurrDate) - Datepart(week, @StartDate))%@WeeklyCycle)
-- Mod % performs the same function as the broken out logic below in notes
END
if @ValidWeek = 0
BEGIN
-- If the schedule isn't set to play on every day of the week...
IF (@DaysOfWeek & 128 != 128)
BEGIN
-- We're going to check to make sure that today is an applicable day to play the presentation
-- If so, we'll move on to check into the date/time intervals. If not, we'll return false.
DECLARE @DayOfWeek NVARCHAR(10)
SELECT @DayOfWeek = DATENAME(dw, @CurrDate)
IF (@DayOfWeek = 'Sunday' AND (@DaysOfWeek & 64 != 64))
RETURN 0
IF (@DayOfWeek = 'Monday' AND (@DaysOfWeek & 32 != 32))
RETURN 0
IF (@DayOfWeek = 'Tuesday' AND (@DaysOfWeek & 16 != 16))
RETURN 0
IF (@DayOfWeek = 'Wednesday' AND (@DaysOfWeek & 8 != 8))
RETURN 0
IF (@DayOfWeek = 'Thursday' AND (@DaysOfWeek & 4 != 4))
RETURN 0
IF (@DayOfWeek = 'Friday' AND (@DaysOfWeek & 2 != 2))
RETURN 0
IF (@DayOfWeek = 'Saturday' AND (@DaysOfWeek & 1 != 1))
RETURN 0
END
-- No need to split date-only part because before the start time on the start date would still be the same result, but the conversion would hurt performance.
select @returnval = case when @CurrDate between @StartDate and @EndDate AND convert(varchar,@CurrDate,108) between convert(varchar,@StartDate,108) AND convert(varchar,@EndDate,108) then 1
else 0 end
END
return @returnval



END