I have a situation that is very difficult to solve. Given the following tables:
Person -> Person_Shift <- Shift
create table person (id int not null, firstname varchar(32) not null, lastname varchar(32) not null, skill_level int not null)
create table shift (id int not null, shiftname varchar(32) not null, sortkey int not null)
create table person_shift(id int not null, personid int not null, shiftid int not null, shiftdate datetime not null)
The person table has 10,000+ names in it. The shift table currently has 7 shifts (e.g., early morning, morning, early afternoon, afternoon, etc.). The join table includes one record for each shift that a person is available for a given date.
The person table also includes a skill level, 1, 2 or 3.
A "meeting" is a shift on a given day.
The goal is to find the same persons who are available to attend the same set of meetings. The meetings can be spread across several days and shifts, so long as all the people are the same at each meeting.
The inputs are:
# of people (these people must be available for all meetings)
# of meetings
Minimum # of skill level 1 people
Minimum # of skill level 2 people
Minimum # of skill level 3 people
A typical example would be:
View Complete Post