Scheduling application - detecting conflicts?

Hi guys!
I have a scheduling application which allows the user to reserve timeslots
for various things but Im 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 Im
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 dont overlap the existing start or end times, and are
actually in between the existing start and end times, it doesnt get caught.

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

Any help or suggestion greatly appreciated!

Re: Scheduling application - detecting conflicts?

You currently appear to be checking to see if each of the existing scheduled
dates falls between the new reservation dates. You need to do it the other
way around. Have a look at this pseudo-code and see if it helps

if new-start <= old-end and new-end >= old-start then
conflict
else
ok

Hint: draw some time-lines so you can see visually if it works for all
conditions.

--
--Mark Schupp


"Jake" wrote in message
news:%23Py42WDoFHA.1468@TK2MSFTNGP12.phx.gbl...
> Hi guys!
> I have a scheduling application which allows the user to reserve timeslots
> for various things but Im 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 Im
> 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 dont overlap the existing start or end times, and are
> actually in between the existing start and end times, it doesnt get
> caught.
>
> How can I adjust or re-do my statement to catch every possible conflict?
>
> Any help or suggestion greatly appreciated!
>
>
>
>
>

Re: Scheduling application - detecting conflicts?

Jake wrote:
> Hi guys!
> I have a scheduling application which allows the user to reserve
> timeslots for various things but Im 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 *

Instead of 'Select *', which retrieves ALL the fields in ALL the rows that
meet your criteria, you should consider using

Select Count(*)

> FROM Schedule WHERE ItemIndex =
> "&strItemIndex&" AND Sched_Date = #"& strSched_Date &"# AND

Your query is a little more complicated than it needs to be due to your
decision to separate the Sched_Date from the Sched_Start and Sched_End
times. If you do not have a need to report all appointments between certain
times regardless of date, then you should consider storing date and time
together.

> (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 Im 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 dont overlap the
> existing start or end times, and are actually in between the existing
> start and end times, it doesnt get caught.
>
> How can I adjust or re-do my statement to catch every possible
> conflict?
>
> Any help or suggestion greatly appreciated!

What you really want to detect is if:
1. an existing scheduled start is between the proposed start and end times
2 an existing scheduled end is between the proposed start and end times
3. the proposed start time is between existing scheduled start and end times

If somebody twisted my arm to prevent me from using saved parameter queries,
here is how I would do this:

dim lngItem,dDate, dStart, dEnd, arParms
lngItem=clng(strItemIndex)
dDate=cdate(strSched_Date)
dStart=cdate(strSched_Start)
dEnd=cdate(Sched_End)
schedcheck = "Select count(*) FROM Schedule " & _
"WHERE ItemIndex = ? AND Sched_Date = ? AND (" & _
"((Sched_Start>= ?) AND (Sched_Start <= ?)) OR" & _
"((Sched_End>=?) AND (Sched_End <= ?)) OR" & _
"((?>= Sched_Start) AND (?<= Sched_End)))"
arParms = Array(lngItem, dDate, dStart, dStart, dEnd, dEnd, _
dStart, dStart)

dim cn, cmd, rs
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & strdb_path_and_filename
set cmd=createobject("adodb.command")
cmd.CommandText=schedcheck
set cmd.ActiveConnection=cn
set rs=cmd.execute(,arParms,1)
if rs(0) > 0 then
'schedule conflict exists
else
'no schedule conflict exists
end if

I suspect that if no conflict exists, that your next task will be to insert
a new record into the table. You can combine these tasks into a single query
like so:

insert into Schedule(ItemIndex,Sched_Date,Sched_Start,Sched_End)
select top 1 FROM Schedule WHERE NOT EXISTS
(SELECT * FROM Schedule WHERE ...)

dim lngItem,dDate, dStart, dEnd, arParms
'initialize data variables as above, then
sInsert== "INSERT INTO Schedule(ItemIndex," & _
"Sched_Date,Sched_Start,Sched_End) " & _
"SELECT TOP 1 ?,?,?,? " & _
"FROM Schedule WHERE NOT EXISTS ("
"Select * FROM Schedule " & _
"WHERE ItemIndex = ? AND Sched_Date = ? AND (" & _
"((Sched_Start>= ?) AND (Sched_Start <= ?)) OR" & _
"((Sched_End>=?) AND (Sched_End <= ?)) OR" & _
"((?>= Sched_Start) AND (?<= Sched_End))))"
arParms = Array(lngItem, dDate, dStart, dStart, _
lngItem, dDate, dStart, dStart, dEnd, dEnd, _
dStart, dStart)

dim cn, cmd, lngRecs
lngRecs = 0
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & strdb_path_and_filename
set cmd=createobject("adodb.command")
cmd.CommandText=schedcheck
set cmd.ActiveConnection=cn
cmd.execute lngRecs,arParms,129
if lngRecs = 0 then
'schedule conflict exists
else
'no schedule conflict exists - data was inserted
end if


HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Scheduling application - detecting conflicts?

Oops. just realized that this is still more complicated than it needs to be
(see Mark's post). Here's a revision:

dim lngItem,dDate, dStart, dEnd, arParms
lngItem=clng(strItemIndex)
dDate=cdate(strSched_Date)
dStart=cdate(strSched_Start)
dEnd=cdate(Sched_End)
schedcheck = "Select count(*) FROM Schedule " & _
"WHERE ItemIndex = ? AND Sched_Date = ? AND (" & _
"Sched_End >= ? OR Sched_Start <= ?)"
arParms = Array(lngItem, dDate, dStart, dEnd)"

dim cn, cmd, rs
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & strdb_path_and_filename
set cmd=createobject("adodb.command")
cmd.CommandText=schedcheck
set cmd.ActiveConnection=cn
set rs=cmd.execute(,arParms,1)
if rs(0) > 0 then
'schedule conflict exists
else
'no schedule conflict exists
end if

I suspect that if no conflict exists, that your next task will be to insert
a new record into the table. You can combine these tasks into a single query
like so:

insert into Schedule(ItemIndex,Sched_Date,Sched_Start,Sched_End)
select top 1 FROM Schedule WHERE NOT EXISTS
(SELECT * FROM Schedule WHERE ...)

dim lngItem,dDate, dStart, dEnd, arParms
'initialize data variables as above, then
sInsert== "INSERT INTO Schedule(ItemIndex," & _
"Sched_Date,Sched_Start,Sched_End) " & _
"SELECT TOP 1 ?,?,?,? " & _
"FROM Schedule WHERE NOT EXISTS ("
"Select * FROM Schedule " & _
"WHERE ItemIndex = ? AND Sched_Date = ? AND (" & _
"Sched_End >= ? OR Sched_Start <= ?)"
arParms = Array(lngItem, dDate, dStart, dEnd))"

arParms = Array(lngItem, dDate, dStart, dStart, _
lngItem, dDate, dStart, dEnd)

dim cn, cmd, lngRecs
lngRecs = 0
set cn=createobject("adodb.connection")
cn.open "provider=microsoft.jet.oledb.4.0;" & _
"data source=" & strdb_path_and_filename
set cmd=createobject("adodb.command")
cmd.CommandText=schedcheck
set cmd.ActiveConnection=cn
cmd.execute lngRecs,arParms,129
if lngRecs = 0 then
'schedule conflict exists
else
'no schedule conflict exists - data was inserted
end if
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.