|
I promised to provide more details of the query (or the
function as it is). Here goes. Scenario: A chemotherapy regimen requires
chair time and nursing time. A patient might sit in the chair for three hours
but the nurse only has to be with them for the first hour. Therefore, nurses
can manage multiple chairs at a time. Each regimen has a different time
requirement. To efficiently manage our chair and
nursing resources, we want to schedule against these constraints. Our room
currently has 17 chairs and around 8 nurses per day. We administer several
hundred different regimens and the time for each regimen varies based on the
day of the regimen as well as the course. All of these variables are entered
and maintained through a web application I wrote. Scheduling algorithm: Written in PostgreSQL (naturally), the algorithm
is a single function call. It gathers the data for a day into a temporary table
and cycles through each appointment. Appointments are scheduled in the following
order: locked appointments (previously scheduled and assigned to a nurse and
chair), reserved appointments (a desired time slot has been selected), open
appointments (ordered by the required chair time descending and the required
nurse time descending). Here’s the busy part that loops through each
appointment. The table definition follows. Anything beginning with an
underscore is a declared variable. -- Reserved and unscheduled appointments. FOR _APPOINTMENT IN SELECT * FROM MATRIX_UNSCHEDULED WHERE
APPT_STATUS <> 'L' ORDER BY ROW_NUM -- Initialize the variables for this record. RAISE NOTICE 'Status ''%'' - %',
_APPOINTMENT.APPT_STATUS, _APPOINTMENT; _AVAILABLE := null; select into _UNIT_INTERVALS, _NURSE_INTERVALS,
_UNIT_REQUIRED, _NURSE_REQUIRED _APPOINTMENT.total_unit_time / 5, _APPOINTMENT.total_nurse_time / 5, (_APPOINTMENT.total_unit_time || '
minutes')::INTERVAL, (_APPOINTMENT.total_nurse_time || '
minutes')::INTERVAL; -- Find the first available row for the required
unit and nurse time. select into _AVAILABLE unit.row_num from ( select m1.row_num from matrix m1, matrix m2 where m1.unit_id =
m2.unit_id and
m1.nurse_id = m2.nurse_id and
m1.unit_scheduled = false and
m2.unit_scheduled = false and
(_APPOINTMENT.reserved_time is null or m1.timeslot = _APPOINTMENT.reserved_time) and
m2.timeslot between m1.timeslot and (m1.timeslot + _UNIT_REQUIRED) group by m1.row_num having count(m2.row_num)
= _UNIT_INTERVALS + 1 ) unit, ( select m1.row_num from matrix m1, matrix m2 where m1.unit_id =
m2.unit_id and
m1.nurse_id = m2.nurse_id and
m1.nurse_scheduled = false and
m2.nurse_scheduled = false and
(_APPOINTMENT.reserved_time is null or m1.timeslot =
_APPOINTMENT.reserved_time) and
m2.timeslot between m1.timeslot and (m1.timeslot + _NURSE_REQUIRED) group by m1.row_num having count(m1.row_num)
= _NURSE_INTERVALS + 1 ) nurse where nurse.row_num = unit.row_num order by unit.row_num limit 1; -- Assign the time, unit, and nurse to the
unscheduled appointment. update matrix_unscheduled set appt_time = matrix.timeslot, unit_id = matrix.unit_id, nurse_id = matrix.nurse_id, appt_status = 'S' from matrix where schedule_appt_id =
_APPOINTMENT.schedule_appt_id and matrix.row_num = _AVAILABLE; -- Mark the unit as scheduled for that time. update matrix set unit_scheduled = true from (select timeslot, unit_id from matrix
where row_num = _AVAILABLE) m2 where matrix.unit_id = m2.unit_id and matrix.timeslot between
m2.timeslot and (m2.timeslot + _UNIT_REQUIRED); -- Mark the nurse as scheduled for that time. update matrix set nurse_scheduled = true from (select timeslot, nurse_id from matrix
where row_num = _AVAILABLE) m2 where matrix.nurse_id = m2.nurse_id and matrix.timeslot between
m2.timeslot and (m2.timeslot + _NURSE_REQUIRED); END CREATE TABLE matrix_unscheduled ( row_num serial NOT NULL, schedule_appt_id integer NOT NULL, appt_time timestamp without time zone, reserved_time timestamp without time zone, appt_status character(1) NOT NULL, unit_id integer, nurse_id integer, total_unit_time integer NOT NULL, total_nurse_time integer NOT NULL, CONSTRAINT pk_matrix_unscheduled PRIMARY KEY (row_num) ) WITH (OIDS=FALSE); CREATE TABLE matrix ( row_num serial NOT NULL, timeslot timestamp without time zone NOT NULL, unit_id integer NOT NULL, nurse_id integer NOT NULL, unit_scheduled boolean NOT NULL, nurse_scheduled boolean NOT NULL, CONSTRAINT pk_matrix PRIMARY KEY (row_num) ) WITH (OIDS=FALSE); There are indexes on “matrix” for “timeslot,unit_id”,
“timeslot,nurse_id”, and “unit_id,nurse_id”.
|
