Ms Access,CF, prevent overlap

[emmim44]

Hi all.
I am trying to prevent an overlap for the same driver for the same day while creating their weekly schedule..st stands for route's start time, et stands for route's end time...If recordcount is zero, means that if it cannot find the overlap, and it will insert the new record in acccess db. my sample st : 12:00:00 AM, et : 10:00:00 AM in db...Any help will appreciate...The query below doesnt work it inserts overlap records..

<cfloop>

SELECT ID,sch_route,sch_time_start,sch_time_end,sch_date FROM
realschedule 

 WHERE trim(sch_day) = 'trim(sch_day)' And driver_id
=Driver_ID And ( (sch_time_start between
#"&FormatDateTime(st)&"# AND
#"&FormatDateTime(et)&"#) or (sch_time_end between
#"&FormatDateTime(st)&"# AND
#"&FormatDateTime(et)&"#) )

</cfloop> 

[Guest]

I would start out by using <cfqueryparam> and making sure the variables are appropriately typed. This is good for performance and security reasons, and a lot of times with date comparison it could be a problem with the timestamp being disregarded and only the data portion considered. See attached.

[Dan_Bracuk]

storing the sch_day as char instead of date is your first problem.

[emmim44]

I have a date field in this format "12-Nov-07" but it has nothing to do with st/et time...Because I schedule drivers based on their availability M-Sunday....Lets say A driver may be available M and Tuesday from 11:30 Am - 7.30 PM....And he a had route knowledge of several places (NJ-NY, Long island-Queens,etc.)..And assume the first route is open M-Sunday from 12PM- 5PM, and 2nd is open from 11:45 - 6 PM....so driver should get only one of these routes since those routes run time falls between his availability and time overlap....What William At FAA suggest is clean way to do it but still doesnt prevent overlapps...
Lets say you have these st - et [ 11:45:00 AM-7:45:00 PM,5:15:00 AM -10:15:00 AM,1:45:00 PM-7:30:00 PM], and insert does as that st-et order...It does insert when I use my statement...but when u look at times carefully, these two overlap { 11:45:00 AM-7:45:00 PM and 1:45:00 PM-7:30:00 PM ]....Why ????

[Guest]

I think I'm a little confused about your database schema and data types. I had assumed sch_time_start was a timestamp (including a date) and I wasn't even sure what sch_day was. It looks like you really have a couple of columns to capture date/times.

I imagine what you really want is a scheduling table that contains a record for each scheduling entry a driver has. The start time and end time could each be a single column, the table might be something like this:

scheduleId: long (arbitrary primary key)
driverId: long (FK for the driver)
routeId: long (FK for the route)
scheduleStart: timestamp (this includes a date AND time value)
scheduleEnd: timestamp (same note as above).

The date really needs to be considered as well as the time if you want to check for overlap. It is much easier to do this in one column versus having separate fields for dates - it is also good for flexibility (if a driver goes on a multi-day trip).

If you can't/won't adjust your database schema, can you at least provide details for each field?

[Guest]

To test this, I created a real simple Access database with my schema and a couple of records. Once I did that I was able to test a couple of scenarios (times falling inside and outside the existing schedule) and the few I checked returned correctly - either no records when the driver is available or one or more records when the driver is busy.

Obviously my test date variables changed as necessary from test to test. I hope this helps.

[emmim44]

Example:
I have Sch_date as "05-Nov-07" as date, and sch_day as "Monday" as text ...Means that each date corresponds to the day...And sch_time_start and sch_time_end are stored as date/time...example: 12:00:00 AM..
How will I use that query then ....since sch_time_start/sch_time_end only contains time, can I make concat with the [sch_date +sch_time_start ] in db level..How will i do that ?

[Guest]

My point is that the DB design is inherently flawed, which is creating unecessary extra coding problems. First of all, why have a field for sch_date and sch_day when you can programatically determine the day from the date. For instance in CF you could use the datePart() function. You can do the same thing in Access. So there are two pieces of redundant data.

My next point is why store the sch_date separately from the sch_time? Really it is a date time value, but in the sch_time_start/end fields you are most likely storing a date and time, but attempting to ignore the date part. However, when you're running your comparisons, Access does not ignore the data portion.

Does this make sense? I would really recommend fixing your DB schema, particularly because it is so easy to do in Access. If you are using a good design pattern with separation of query logic it should take less than 30 minutes to update all your relevant code, even if there's lots of it. The headaches it will save you from down the road will be well worth it.

[emmim44]

it makes sense..is there way to concat the sch_date with sch_time_start /sch_time_end while comparing...
example:
select * from realschedule
where (sch_date + sch_time_start) between st.......

If i do what you suggest, it will take lots of code to change....

[Newsgroup_User]

One possible way to meet in the middle is to create new field(s) that
concatenate the values of the existing fields into the type necessary to
meet these requirement. You can use these new fields without changing
the old ones until such a time that one can update the application code
to the more efficient fields.

[emmim44]

Exactly that what i did :DDD now...but there is start time : 10 :45 PM, and end_time = 6:30 AM where lets say 10:45 PM is on saturday and then 6.30 AM is sunday....How will I fix that ?
Another condtion is the date I saved in db is 12:00 AM- 5.30 AM on monday, and when I built the full date time....starts date return 11/05/07 and end date returns 11/05/07 5:30:00 AM....Why is not complete ?