/* ---------- Comment: Staff Scheduling Model --------------------
Requires an Access Database with three tables:
1. Requirements: Lists each "day" and the requirements for the day
2. Shifts: Lists the names of the shifts
3. ShiftDays: Lists the days in each shift
This model determines how many employees to assign to each shift
so as to meet each days requirements with a minimum total number
of employees
---------------------------------------------------------------- */
/* ================================================================
MODEL SETS AND PARAMETERS
================================================================ */
/* ----------------------------------------------------------------
The set of days
---------------------------------------------------------------- */
set DAYS;
/* -------------------------------------------------------------
Daily requirements
------------------------------------------------------------- */
param Requirement{DAYS};
/* ----------------------------------------------------------------
The set of shifts
---------------------------------------------------------------- */
set SHIFTS;
/* ----------------------------------------------------------------
The days each shift works. This is a two dimensional set. Its
elements are of the form (shift, day) where shift is in SHIFTS and
day is in DAYS.
---------------------------------------------------------------- */
set SHIFTDAYS within SHIFTS cross DAYS;
/* ================================================================
READ THE DATA
================================================================ */
/* ----------------------------------------------------------------
Define a table called DayTable that brings data IN to AMPL via
ODBC from the datasource StaffingData. You will need to set this
datasource up in the Windows Control Panel. It should point to
our Access Database StaffScheduling.mdb. DayTable reads the Day
and Requirement fields from the table Requirements. Day is in []'s
because it is an index, i.e., we will read a Requirement for each
day. This also defines the set DAYS.
---------------------------------------------------------------- */
table DayTable IN "ODBC"
"DSN=StaffingData"
"Requirements":
DAYS = 0;
/* ================================================================
MODEL OBJECTIVE AND CONSTRAINTS
================================================================ */
/* ----------------------------------------------------------------
Objective: Minimize the number of employees
---------------------------------------------------------------- */
minimize TotalStaff:
sum{shift in SHIFTS} Staff[shift];
/* ----------------------------------------------------------------
Meet each days requirements
---------------------------------------------------------------- */
s.t. MeetRequirements{day in DAYS}:
sum{(shift, day) in SHIFTDAYS} Staff[shift] >= Requirement[day];
/* ================================================================
SOLVE THE PROBLEM
================================================================ */
solve;
/* ================================================================
REPORT THE ANSWER
================================================================ */
table StaffOut OUT "ODBC"
"DSN=StaffingData"
"StaffOut":
{shift in SHIFTS: Staff[shift] > 0} -> [shift], Staff[shift]~Staff;
write table StaffOut;