We have developed a manpower scheduling application for one of our
clients. The application allows the client to maintain independent
weekly work schedules for a number of customers and locations. Now
after a year of use and some 10,000 schedules with about 250,000
individual schedule items the client wants us to do conflict
recognition to ensure that the same employee(s) are not assigned to
multiple customer/locations for the same date and time period.

The application is in PERLscript under IIS and uses the JET engine for
the database and OLE/ADODB for accessing the database.

We have implemented some conflict detection and it works in most cases.
We do an SQL that tests for the start date/time and end date/time
within the time ranges for all the other items in the database for the
same date/time range at all other locations i.e.

"SELECT sm_location_id, sm_customer_id, sd_id, sd_date, sd_time_in,
sd_time_out
FROM Schedule_Master, Schedule_Detail
WHERE sm_location_id <> '$locationID' AND sd_master = sm_id AND
sd_employeeid = '$sd_employeeid' AND ((($sd_datetime_in BETWEEN sd_date
& sd_time_in AND
sd_date_out & sd_time_out) OR ($sd_datetime_out BETWEEN sd_date &
sd_time_in AND sd_date_out & sd_time_out)))"

All date fields (sd_date and sd_date_out) are stored as text in ISO
8601 format (YYYYMMDD) and the time is stored as text in zero-filled
decimal fractional values (e.g. 3:30AM is 03.50, 5:05 PM is 17.08
etc.). The input query values (e.g. $sd_datetime_in and
$sd_datetime_out) are configured as YYYYMMDDHH.MM.

This conflict detection works fine and catches situations where an
employee appears in more than one customer/location with a start and/or
end time that overlaps with another schedule item. However, we've
found a number of cases where an employee start/end time is entirely
within the start/end time range of another schedule item e.g.:

Sked Empl Locn Start End
A 1234 5678 09:00 15:00
B 1234 3456 08:00 17:00

In this case the time range for Sked A is entirely within the range for
Sked B. Sked A flags the conflict with Sked B because the start and end
times are within the range for Sked B. However, Sked B does not flag a
conflict with sked A because Sked A begins and ends entirely within
Sked B. Similarly, if the schedule begins one day and crosses over
midnight one of the schedules will detect the overlap but the other
will not.

Can anyone suggest a method that will detect the conflict in all cases
without tripping false conflicts? The Schedule Detail table currently
has about 250,000 items in it so significant modifications to the
database and/or fields are questionable :-)

ClipperMiami (AT) gmail (DOT) com wrote:

[snip]

The condition that I use is
(datehigh2<datelow1 or datelow2>datehigh1)
where datelow1 to datehigh1 is one date range and datelow2 to
datehigh2 is the other. If the result is true, there is no overlap;
if false, there is.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.