Scheduling application - logic problem

[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!

[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?

"Jake" <sp******@alltel.net> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

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!

[Chris Hohmann]

"Jake" <sp******@alltel.net> wrote in message
news:%2***************@TK2MSFTNGP10.phx.gbl...

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