Validating Date-Time Ranges in a Schedule Database
in [Perl]
|
Prev: One-Liner Help with -e + ksh heredoc
Next: Please help me pass an array from VBA to Perl and populate it.Newbie at wits' end!
From: John on 4 Oct 2006 17:39 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 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 this 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 & sd_time_out) OR ($sd_datetime_out BETWEEN sd_date & sd_time_in AND sd_date & sd_time_out)))" This 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. Can anyone propose a change to the SQL that will detect the conflict in both cases without tripping false conflicts?
From: A. Sinan Unur on 4 Oct 2006 21:53
"John" <ClipperMiami(a)gmail.com> wrote in news:1159997978.837729.104600 @e3g2000cwe.googlegroups.com: > Can anyone propose a change to the SQL that will detect the conflict in > both cases without tripping false conflicts? > First off, it looks like you have difficulty diagnosing the most effective way of solving your problem. This group is about Perl, you are more likely to get SQL help here. I will point out that if you had designed your tables differently, you would have been able to solve this problem in a more straightforward way in SQL. My guess is, if you post your SQL and table design on comp.databases.theory, you will get very illuminating answers. If you want to solve your problem in Perl (although I would recommend reconsidering the design of the database), you'll need to read the posting guidelines, and post some Perl code so we know what to help you with. Sinan -- A. Sinan Unur <1usa(a)llenroc.ude.invalid> (remove .invalid and reverse each component for email address) comp.lang.perl.misc guidelines on the WWW: http://augustmail.com/~tadmc/clpmisc/clpmisc_guidelines.html |