Reoccuring Scheduling Logic (continued)

When designing a scheduling system, if your business rules allow you to have a one to many relationship between scheduled times and items being scheduled you are going to need to know which record is the one that should determine if the record should play or not. For example, a pattern may not fit neatly into a one week range (i.e. Mon, Wed, Fri) and your pattern may require wrapping your schedulE across weeks with something running from Wed-Sun. Remember from our database functions (or consulting a the calendar on the wall) that weeks begin on Sunday, so a schedule running from Wed - Sun would require week A to run from Wed-Sat and week B to run from Sunday AM to PM. Now you have two valid date ranges concerning one file to schedule and you need to determine whether to play or suspend the schedule.

The use cases are as follows:
Schedule A is valid, Schedule B is not (so we play)
Schedule B is valid, Schedule A is not (so we play)
Schedule A and B are both not valid (so we suspend play)

We can either get all of the affected records, stuff them into a cursor and turn off the records that need switching off first, then turn on all the ones that should be active. That is fairly straightforward, procedural programming and if you spend most of your time coding in C# or VB, as opposed to in the database, then you'll approach this from a coders point of view: build an array and cycle through it. You might, if you're a little more advanced consider logic to facilitate some sort of truth table and test for the true condition. However, remember we are working in a database, so the simplest solution would be to convert the bit returned from the scheduling function and sum the results, grouped by the items being scheduled.


SELECT col1, col2, col3
sum(cast(dbo.fn_Check_Schedule(StartDate, EndDate, DaysOfWeek, getDate(), WeeklyCycle ) as int) )sched
FROM t_ItemsBeingScheduled
group by col1, col2, col3

Now you'll get one record per item being scheduled.