Not your typical newbe sorta help, but at this point I sure do feel like one.

Hopefully this will make sense.

Table 1
TblStaff
ID
Firstname Surname

Table 2
TblStaffWorkPatterns
StaffID
PatternID
DateStart
DateEnd

Table 3
TblWorkPatterns
ID
DayOfWeek
StartTime
EndTime

Table 2 hold a historic record of the workpattens each staff has been assigned to over the course of their life. If a NULL valude exists in DateEnd, it assumes it as still currently running.

Table 3 hold the working patterns for each working day the staff is scheduled to work, similar to the following below:

Monday 12:00 17:00
Tuesday 13:00 18:00
Wednesday 14:00 15:00
Friday 09:00 15:00

Hence the staff would be scheduled to work, Mon, Tue, Wed & Fri for the above listed times.

Now the problem...


This works fine, however a client of ours have their staff alternate their working hours each week. Meaning, one week they work this pattern, next week they work another, thrid week its back to pattern 1 etc... Alternating between the two weekly patterns.

Now this is ofcorse accomplished via Table 2 enterning a DateStart & DateEnd, however this means it has to be done either, each week, or processed in advance for the next "X Number" of weeks.

Obviously this is messy since it occupies a row in Table 2 for each week.

I have therefore come to a halt and am not sure the best way to aproach this. I could do 1 of two things (I think)...

1) Add To Table 3 two extra fields for the alternating shift Times e.g:
-StartTimeAlt
-EndTimeAlt

However I am still not sure how I would be able to tell which rotation week the staff will be on. Meaning do I read fields:
(StartTime & EndTime)
or
(StartTimeAlt & EndTimeAlt)

I might be over looking something obvious thats hammering my head, any assistance would be greatly apreciated...

Thanks...

ijwalla

Could you just have a flag in Table 3 (TblWorkPatterns) that states whether this pattern applies on every week, just odd weeks or even weeks.

Everything would run as before if the Flag='every' but if the your select would only pull records where the Flag='even' on even numbered weeks and ignore the odd records. Obviously it would do the opposite on odd weeks.

You don't say what type of database you use but all can give you a week number (1-52).

Mike

I would add a new table like:

TblCycles
CycleID
CycleLength
CycleModulo

Cycles would allow you to describe repetition easily. The CycleLength would describe how many units of time (weeks in this case) a cycle lasted before it repeated itself. The CycleModulo would describe where in the cycle the week appeared (0 would be the last week in the cycle, 1 would be the first week, 2 for the second, etc). All cycles should be computed from some base date, or you will get into trouble every year when you start the week count over because you'll have two odd weeks in a row. For patterns that repeated every week, the CycleLength would be 1 and the CycleModulo would be 0. A two week cycle would have two rows (2, 1) and (2, 0).

You could then add the CycleID to each WorkPattern in order to tell which cycle applied to that pattern.

-PatP

Good point. I guess you could get round that by getting the number of days since 1 Jan 2000 and then divide by 7 but ...

Mike