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