Roster prediction

[Mark Reed]

Hi all,
The background to this problem is that I have been asked to create a Db
that monitors holidays within the warehouse. What the users wish to do is to
type in a colleague number (unique) and then the DB will say either "yes" or
"no" if it is ok to authorise. The yes or no will be based on how many other
people (as a percentage) will be rostered in on that day and how many have
holidays booked on that day.

To do this, I need to know who will be in on what day up to 2 years in
advance.

The roster rolls over every 8 weeks and is broken into 5 teams and 8 weeks.
So I have 740+ employees split between 40 shift patterns.
The employee table will have the unique colleague number along with which
team and week they are assigned to.

Using excel, I worked out the shift patterns and split it into 4 columns,
shift date, team, week & on/off. This equated to over 30.000 records going
up to 2007. When this is multiplied by the number of employees, the record
count goes up to over 2,000,000 records!!!!! This is obviously a bad design
but I cant think of a way to predict what so many people will be doing in
the future on so many shift patterns.

Example of the shift patterns will be:

Team 1
Sat Sun Mon Tue Wed Thur Fri
Week 1 Earlies Earlies Earlies Off Off Lates Lates
Week 2 Lates Lates Lates Off Earlies Earlies Earlies
Week 3 Earlies Off Lates Lates Lates Lates Lates
Week 4 Off Off Earlies Earlies Earlies Earlies Earlies
Week 5 Off Off Lates Lates Lates Lates Lates
Week 6 Lates Off Off Earlies Earlies Earlies Earlies
Week 7 Earlies Earlies Off Lates Lates Lates Lates
Week 8 Off Earlies Earlies Earlies Earlies Earlies Earlies

An 8th of the people assigned to team 1 will be on week 1, another to week 2
and so forth.
Could anyone suggest a way in which I can predict what shift an employee
will be working without have a table with all the data in it?

Any help would be gratefully accepted as I am at a standstill.

Thanks in advance,

Mark

[Salad]

Mark Reed wrote:

Hi all,
The background to this problem is that I have been asked to create a Db
that monitors holidays within the warehouse. What the users wish to do is to
type in a colleague number (unique) and then the DB will say either "yes" or
"no" if it is ok to authorise. The yes or no will be based on how many other
people (as a percentage) will be rostered in on that day and how many have
holidays booked on that day.

To do this, I need to know who will be in on what day up to 2 years in
advance.

The roster rolls over every 8 weeks and is broken into 5 teams and 8 weeks.
So I have 740+ employees split between 40 shift patterns.
The employee table will have the unique colleague number along with which
team and week they are assigned to.

Using excel, I worked out the shift patterns and split it into 4 columns,
shift date, team, week & on/off. This equated to over 30.000 records going
up to 2007. When this is multiplied by the number of employees, the record
count goes up to over 2,000,000 records!!!!! This is obviously a bad design
but I cant think of a way to predict what so many people will be doing in
the future on so many shift patterns.

Example of the shift patterns will be:

Team 1
Sat Sun Mon Tue Wed Thur Fri
Week 1 Earlies Earlies Earlies Off Off Lates Lates
Week 2 Lates Lates Lates Off Earlies Earlies Earlies
Week 3 Earlies Off Lates Lates Lates Lates Lates
Week 4 Off Off Earlies Earlies Earlies Earlies Earlies
Week 5 Off Off Lates Lates Lates Lates Lates
Week 6 Lates Off Off Earlies Earlies Earlies Earlies
Week 7 Earlies Earlies Off Lates Lates Lates Lates
Week 8 Off Earlies Earlies Earlies Earlies Earlies Earlies

An 8th of the people assigned to team 1 will be on week 1, another to week 2
and so forth.
Could anyone suggest a way in which I can predict what shift an employee
will be working without have a table with all the data in it?

Any help would be gratefully accepted as I am at a standstill.

Thanks in advance,

Mark

I don't have a formula but I'm sure one can be devised.

I'd probably have a lookup table of your week1/8 so folks no what the
schedule is for that particular team/week once the calc determines the week.

I would have a StartDate someplace. IOW, you need a date to start
from/compare to a future date. For example, you could use the HireDate
as the start. You would also want to store which week the person starts
on. For example, when hired, this person is on Team 1, week 6.

Note: If the Week the person starts on based on the hire date can be
calculated, you could also store another start date to a static table.
This table could simply have 1 field, 1 record that stores a date. Then
you could subtract that start date from the hire date to determine the
week that this person starts in. (I don't know how you assign a person
to the team/start week)

You would probably need DateDiff() to calculate the number of days from
start to future.
intWeeks = int(DateDiff("d",[HireDate],[FutureDate])/7)
You now have the number of weeks between hire date/future date.

You would want to know how many timeweeks to increment by.
intTimeWeeks = intWeeks MOD 8

This will give you a remainder from the number of weeks divided by 8.
Thus if person was in week1 when hired and the result of weeks mod 8 was
0, then he would be in week1 in the future. If the mod was 7, it would
be week 8.

You'd have to make an adjustment for those, when hired, are on a diffent
shift. Let's take a look at Week5. If intTimeWeeks had a mod of 0 to
3, you could add that to get week5-8. But if the mod result was 4-7,
you need to adjust to get week1-4. Maybe something like
WeekTeamAtFutureDate = [WeekTeamAtStartOfHire] + intTimeWeeks
If WeekTeamAtFutureDate > 8 then
WeekTeamAtFutureDate = WeekTeamAtFutureDate -8
Endif

I think if you take these concepts and fiddle around with it a bit you
should be able to cobble something together. You also need to adjust if
a person starts on week1 and is "promoted" to Week7 in the future due to
an organization change.