Scheduling Table with exceptions (1 Viewer)

[ASherbuck]

I have a normal schedule table in my database that stores the open/close hours for each business unit location.

The problem is some locations are timeshares and some have different business units every other week, every 2 Tuesday, the 1st and 3rd weeks of the month.

I figured I would make an exception table noting all of these somehow and after building a calendar/report plug in the exceptions.

Has anyone worked on this or come up with a more elegant solution?

 

[CJ_London]

you'll need to provide more information.

the open/close hours - is this simply two times applied 7 days a week, or weekdays only or one pair of times for Monday another for Tuesday etc. or what?

What is the significance of timeshares?

etc

 

[ASherbuck]

Gotcha, here's some clarification.

I've got a schedule table as such:

tblOperating Hours
| dept_id | day | location | open_time | close_time
|  1        |   1   |       5      |       0900    |    1700
|  2        |   1   |       5      |       0900    |    1700
|  3        |   1   |       4      |       0900    |    1700

Both departments 1 & 2 operating hours are the same, for the same day. Both will make use of the same physical location, on alternating weeks.

One example is on the first and third Monday of the month one team is in the lab. On the second and fourth Monday of the month another team is in the same lab. The lab is a timeshare and I have many labs to account for. The majority are like department 3, one location that is not shared.

For scheduling exceptions like 1 & 2 I'm having a difficult time storing that information. What's more, some don't schedule based on the month, just every other week. So some months they are the first Monday, some months they are the second Monday depending on the number of days in the month.

Does this help? It's difficult for me to explain via text.

 

[CJ_London]

I think you need to create a proper booking system using dates otherwise you will have a horrible algorythm to create - for example using your example, what happens in June 2014 when there are 5 Mondays in the month?

This link will take you an example of an attendance register which may be of help to get you started

http://www.access-programmers.co.uk/forums/showthread.php?t=260034

Failing that 'every other week' requires a start point, 'first Monday' needs to be defined but will clash at some point with an 'every other week' appointment - and what happens on bank holidays? does the department just lose that day?

If you create a table just with a date field populated with say a years worth of consequtive dates then then you can use the weekday function to determine for each day whether it is a Monday, Tuesday or whatever.

 

[ASherbuck]

Thank you for sticking with me.

A starting point was another idea I thought about, I'm glad you reinforced it.

And you're right, months with shorter dates were the problem. It was accounting for the partial 5th week that made this so difficult.

The booking system sounds like a win. I considered making an actual schedule. Put the rules in place and project it X days ahead.

Lots of good stuff for me here, thank you.