Scheduling application - logic problem

J

Jake

Hi, I originally posted in the db group but thought this might be more
appropriate here.

I have a scheduling application which allows the user to reserve timeslots
for various things but I'm having trouble figuring out how to detect if a
reservation being added is conflicting with an existing reservation.
Basically its a logic problem.

I am currently able to detect timeslots that overlap start and/or end times
but cant figure out how to detect other conflicts.

For example, here's my check routine:
schedcheck = "Select * FROM Schedule WHERE ItemIndex = "&strItemIndex&" AND
Sched_Date = #"& strSched_Date &"# AND (Sched_Start BETWEEN # "&
strSched_Start &" # AND # "& dateAdd("n",-1,strSched_End) &" # OR Sched_End
BETWEEN # "& dateAdd("n",1,strSched_Start) &"# AND # "& strSched_End &"#) "

This detects conflicts such as a new reservation being scheduled from 1:00
pm to 4:00 pm and there's already an existing reservation from 2:00 pm to
5:00 pm. Since there is an overlap of times, it catches it, but what I'm
missing is if I have, say an existing reservation from 1:00 pm to 5:00 pm
and someone tries to schedule one from 2:00 pm to 4:00 pm. Since the new
reservation times don't overlap the existing start or end times, and are
actually in between the existing start and end times, it doesn't get caught.

How can I adjust or re-do my statement to catch every possible conflict?

Any help or suggestion greatly appreciated!
 
J

James

Forgive me if I'm wrong/misunderstanding...but wouldn't you just have to
evaluate whether the start or end is between the start and end of the
already scheduled event?

Assume A & B = Start/End for new item
Assume C & D = Start/End for old item

Problems:
A < C & B Between C & D
A Between C & D and B between C & D
A Between C & D and B > D

So couldn't you just say If A Between C & D OR B Between C & D then you have
a conflict?
 
C

Chris Hohmann

Jake said:
Hi, I originally posted in the db group but thought this might be more
appropriate here.

I have a scheduling application which allows the user to reserve timeslots
for various things but I'm having trouble figuring out how to detect if a
reservation being added is conflicting with an existing reservation.
Basically its a logic problem.

I am currently able to detect timeslots that overlap start and/or end
times
but cant figure out how to detect other conflicts.

For example, here's my check routine:
schedcheck = "Select * FROM Schedule WHERE ItemIndex = "&strItemIndex&"
AND
Sched_Date = #"& strSched_Date &"# AND (Sched_Start BETWEEN # "&
strSched_Start &" # AND # "& dateAdd("n",-1,strSched_End) &" # OR
Sched_End
BETWEEN # "& dateAdd("n",1,strSched_Start) &"# AND # "& strSched_End &"#)
"

This detects conflicts such as a new reservation being scheduled from 1:00
pm to 4:00 pm and there's already an existing reservation from 2:00 pm to
5:00 pm. Since there is an overlap of times, it catches it, but what I'm
missing is if I have, say an existing reservation from 1:00 pm to 5:00 pm
and someone tries to schedule one from 2:00 pm to 4:00 pm. Since the new
reservation times don't overlap the existing start or end times, and are
actually in between the existing start and end times, it doesn't get
caught.

How can I adjust or re-do my statement to catch every possible conflict?

Any help or suggestion greatly appreciated!

m.p.i.asp.db was the right group and Mark Schupp's solution was the correct
one. In the future, please indicate which database and version you are using
as well as DDL for the tables involved. Also, please consider using a
parameterized query in lieu of dynamic sql for improved performance and to
avoid sql injection. Finally, please consider explicitly declaring the
columns you would like to retrieve instead of using "SELECT *". Here's a
parameterized query to retrieve a list of conflicting reservations:


[BEGIN Select_Conflicting_Reservations]
PARAMETERS prm_item_index INTEGER, prm_sched_start DATETIME, prm_sched_end
DATETIME;
SELECT <<Explicit Column List>>
FROM Schedule
WHERE ItemIndex = prm_item_index
AND Sched_Start <= prm_sched_end
AND Sched_End >= prm_sched_start
[END Select_Conflicting_Reservations]

Here are some related articles:
http://aspfaq.com/etiquette.asp?id=5003
http://aspfaq.com/etiquette.asp?id=5006
http://aspfaq.com/etiquette.asp?id=5009
http://aspfaq.com/show.asp?id=2096
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top