Multi-Site Staff Scheduling with Excel - The Model

Here's our model loops over the three sites/tabs, solving each individual staffing model:

MODEL:

 

! Uses a loop to solve three staff scheduling

 models, retrieving the data from and writing

 the solution to an Excel workbook;

 

SETS:

 SITES / PLUTO, MARS, SATURN/;

 DAYS / MON TUE WED THU FRI SAT SUN/:

  NEEDS, START, ONDUTY;

ENDSETS

 

SUBMODEL STAFF:

 [OBJROW] MIN = @SUM( DAYS: START);

 

 @FOR( DAYS( D):

    ONDUTY( D) = @SUM( DAYS( D2) | D2 #LE# 5:

      START( @WRAP( D - D2 + 1, @SIZE( DAYS))));

    ONDUTY( D) >= NEEDS( D);

 );

 

 @FOR( DAYS: @GIN( START));

ENDSUBMODEL

 

CALC:

 @SET( 'TERSEO', 2);

 

 @FOR( SITES( S):

    NEEDS = @OLE( '\LINGO12\SAMPLES\DOGS.XLS',

     SITES( S)+'NEEDS');

 

    @SOLVE( STAFF);

 

    @SOLU( 0, ONDUTY, '  On Duty Report: ' + SITES( S));

 

    @OLE( , SITES( S)+'START', SITES( S)+'ONDUTY',

      SITES( S)+'TOTAL') = START, ONDUTY, OBJROW;

 );

ENDCALC

 

END

Model: LOOPOLE

This model may also be found in the Samples folder of the main LINGO folder.

Note: Versions of this example staffing model that interface with an Access database or that interface with text files are also available.  Please see models LOOPODBC and LOOPTEXT.