Need some query help

[zakhan]

I have a table with the following structure:

BookingId StaffId MemberId BookingDate BookingFrom BookingTo Cancelled
117 14 2025 2/8/2005 10:00:00 AM 10:30:00 AM No
118 14 2025 2/8/2005 8:30:00 AM 9:00:00 AM No
119 14 2025 2/8/2005 9:00:00 AM 9:30:00 AM No

Now I have a query:

SELECT *
FROM Bookings
WHERE StaffID='14'
And BookingDate=#2/8/2005#
And ((#12/30/1899 8:0:0# Between BookingFrom And BookingTo) Or
(#12/30/1899 8:30:0# Between BookingFrom And BookingTo))
And (#12/30/1899 8:30:0#<>BookingFrom)
And (#12/30/1899 8:0:0#<>BookingTo)
And Not (Cancelled=True);

This query returns the row with BookingId=118. For the life of me I
cannot figure out why this is happening. Conceptually, the table
contains a bunch of booking appointments. The query will give me a list
of conflicting appointments for a specific prospective appointment.

So basically I'm trying to see if there are any booking conflicts for
the 8-8:30 time slot and I expect this query to return no rows but it
returns 1 row which is the time-slot 8:30-9am

Shouldn't the AND condition 'And (#12/30/1899 8:30:0#<>BookingFrom)'
return FALSE causing the query to return zero rows?

Simple enough as this might seem, I just can't get this to work.

(I have an .mdb file with the table and query if anyone would like the
table and query).

Thanks.

 

[John Spencer (MVP)]

It is possible (not likely) that the times aren't an exact match due to rounding
errors in the way double numbers are stored.

You might try running a query to select on the exact date and time and see what
is returned.

I myself would probably change the query slightly and use the seconds to offset
the time slightly. I think that the offset would be something like:

SELECT *
FROM Bookings
WHERE StaffID='14'
And BookingDate=#2/8/2005#
And (#12/30/1899 7:59:59# Between BookingFrom And BookingTo Or
#12/30/1899 8:30:01# Between BookingFrom And BookingTo)
And Not (Cancelled=True);

You could use the dateadd function to do this so it would be easier to adjust
the time component.

( DateAdd("s",-1,#12/30/1899 8:0:0#) Between BookingFrom And BookingTo Or
DateAdd("s",1,#12/30/1899 8:30:00#) Between BookingFrom And BookingTo)

 

[zakhan]

Thanks for your reply, John.

I was fearing someone would come up with that suggestion but I'm really
trying to avoid adding/subtracting seconds because it does not look
like an elegant solutions and bugs may surface later on.

This seems like such a straightforward requirement that it seems hard
to believe that I cannot compare two date-types together!

John, you mentioned something about doubles. Are you saying the
datetimes are stored as doubles in Access? Moreover, if I have a query
that has the date literal #9:00:00am#, Access always changes this to
#12/30/1899 9:0:0#. Why is this and is there a way of avoiding this?

I just hope I don't have to store my dates as doubles in the table!

Cheers.

 

[Gary Walter]

I added some records so there were conflicts:

Bookings BookingID StaffID MemberID BookingDate BookingFrom BookingTo Cancelled
117 14 2025 2/8/2005 10:00:00 AM 10:30:00 AM No
118 14 2025 2/8/2005 8:30:00 AM 9:00:00 AM No
119 14 2025 2/8/2005 9:00:00 AM 9:30:00 AM No
120 14 2025 2/8/2005 8:00:00 AM 8:30:00 AM No
121 14 2025 2/8/2005 7:30:00 AM 8:15:00 AM No
122 14 2025 2/8/2005 8:10:00 AM 8:20:00 AM No
123 14 2025 2/8/2005 8:15:00 AM 9:30:00 AM No


SELECT
B.BookingID,
B.StaffID,
B.MemberID,
B.BookingDate,
B.BookingFrom,
B.BookingTo,
B.Cancelled
FROM Bookings AS B
WHERE (
((B.StaffID)=14)
AND
((B.BookingDate)=#2/8/2005#)
AND
((B.BookingFrom)<#12/30/1899 8:30:0#)
AND
((B.BookingTo)>#12/30/1899 8:0:0#)
AND
((B.Cancelled)=0)
);

gave me:

BookingID StaffID MemberID BookingDate BookingFrom BookingTo Cancelled
120 14 2025 2/8/2005 8:00:00 AM 8:30:00 AM No
121 14 2025 2/8/2005 7:30:00 AM 8:15:00 AM No
122 14 2025 2/8/2005 8:10:00 AM 8:20:00 AM No
123 14 2025 2/8/2005 8:15:00 AM 9:30:00 AM No

 

[Gary Walter]

Mathematically (from previous post by Michel)

***quote***

Ranges do NOT overlap if
( I assume aStart, aEnd, bStart and bEnd are the intervals):

aStart > bEnd OR aEnd < bStart

they overlap, in part or in full, on the negation of that statement, ie
(Apply De Morgan's law) :

aStart <= bEnd AND aEnd >= bStart

*** unquote ***

so...
the query "should be":

SELECT
B.BookingID,
B.StaffID,
B.MemberID,
B.BookingDate,
B.BookingFrom,
B.BookingTo,
B.Cancelled
FROM Bookings AS B
WHERE (
((B.StaffID)=14)
AND
((B.BookingDate)=#2/8/2005#)
AND
((B.BookingFrom)<=#12/30/1899 8:30:0#)
AND
((B.BookingTo)>=#12/30/1899 8:0:0#)
AND
((B.Cancelled)=0)
);

But, time overlaps are a different beast
as far as within my experience (probably
due to inexactness of way floats are stored
on a computer like John said).

The above query returns the record for the
"wrong" BookingID = 118
just like you were experiencing.

And why the previous query (w/o the "='s")
returns BookingID = 120 doesn't seem to
make sense in a "perfect world."

So...
if I am dealing with floats,
I drop the "='s";
if I am dealing with only the date portion
("left of the period"),
I use the "='s".

good luck,

gary

 

[Gary Walter]

Looking back, it does possibly make sense
*ignoring the float issue*:

if we think your BETWEEN/OR/BETWEEN
were "equivalent to":

((B.BookingFrom)<=#12/30/1899 8:30:0#)
AND
((B.BookingTo)>=#12/30/1899 8:0:0#)

for the record that did not make sense

BookingID BookingDate BookingFrom BookingTo
118 2/8/2005 8:30:00 AM 9:00:00 AM

then

BookingFrom is equal to 8:30 AM
and
BookingTo is greater than 8:00 AM

so BookingID=118 should have been returned.

 

[Douglas J. Steele]

PMFJI, but I don't understand why you don't simply add the BookingDate to
BookingFrom and BookingTo, and do everything together! You can use
(BookingDate + BookingFrom) and (BookingDate + BookingTo), and it should
simplify your comparisons.

And I haven't been following this thread, so I don't know the significance
of the hard coded times, but be aware that you don't need to include the
12/30/1899 in constant times: #8:30:00# and #8:00:00# should work fine. If
in doubt, use TimeSerial(8,30,0) and TimeSerial(8,0,0)

 

[Gary Walter]

Hi Doug,

I must admit I don't understand the
need to simplify.

On the second point, when I entered
the criteria simply as time constants, i.e.,

<#8:30:00 AM#

then copied the SQL for this post,
Access had filled in the date part,
but I lazily just didn't edit it out
when I pasted it into the text.

As always, all "jumping ins" are appreciated.
Many of my responses probably need more
of them!

Thanks,

gary

 

[Gary Walter]

Doug,

Apologies for OT, but...

I was listening to old Eagles album
and thought of this newsgroup and
"addiction to them"..does this sound
familiar?

"You can check out anytime you want,
but you can never leave"

gary

 

[zakhan]

Thanks for the replies.

Doug:
1. I would have used the BookingDate as part of the BookingFrom and
BookingTo as well. Unfortunately, the data model has been done by a
previous developer and there's already too much code depending on this
model for refactoring.

2. I was using the hard-coded times just to illustrate a particular
instance of the query. In practice, I'm running the Access query from
my Visual Basic program and binding the date parameters.

Gary:

This is a very beautiful observation:
****
Ranges do NOT overlap if
( I assume aStart, aEnd, bStart and bEnd are the intervals):
aStart > bEnd OR aEnd < bStart
****
I was doing it on a case basis and checking for 4 separate cases (using
the two between clauses and the two border-line conditions).

I agree that if I only had the BETWEEN/OR/BETWEEN check in there, then
it should return the '118' record. But that's why I had the borderline
checks there (And (#12/30/1899 8:30:0#<>BookingFrom) And (#12/30/1899
8:0:0#<>BookingTo)

So with that in place, it doesn't explain why it returns the '118'
record because clearly the (#12/30/1899 8:30:0#<>BookingFrom) condition
should evaluate to False.

However, it's probably the fact that the dates are stored as double
precision floats as this Microsoft article shows:

http://support.microsoft.com/default.aspx?kbid=210276

I will try to use your query 'without the =s' and see if that works for
me.

Thanks

 

[Gary Walter]

I could be wrong of course,
but that is the data model I
would have used.

The "Set Appointments" form would
probably have a calendar control
where the user sets a date,

then 2 combo boxes for "From" and "To",
with Before/After_Update code behind them
to ensure "To" is greater than "From", and
probably limiting choice in one combo
when time is selected in other combo.

I'm sorry...it just makes sense to me.

You are right (I missed the inequalities).
In JOINS or equalities/inequalities of
times (floats), I usually format them

WHERE
Format([sometime],"hh:nn:ss AMPM") <>
Format([someothertime],"hh:nn:ss AMPM")

Of course, I could be wrong....

gary

 

[zakhan]

The UI I'm using is actually graphical. The user selects a date from a
Calendar control and this displays to him the schedule for the day (I'm
using the MSFlexGrid control for doing the display). This schedule has
all the current appointments and also shows when the staff is on-duty
for prospective appointments.

It's even more frustrating for the user to double click an open time
slot and try to do a booking and get the message 'This time-slot has
already been reserved' (due to the issue we've been discussing).

I have not tried your query yet, but if that doesn't work, I will
probably use the hack that John spoke about right at the start.

Thanks all.

 

[zman]

Just a final post to tell everyone that I went with John's initial
suggestion (except that I think the add/subtract second should be
reversed in John's query).

Gary's query worked for the particular instance that we were discussing
(ie. trying to book an empty slot between two slots that are reserved)
but at the same time, it allowed me to over-book on an existing
appointment!

Cheers.

 

[Gary Walter]

Sorry zman,

You gave some data,
I gave a query that I thought would find
all the records in that data that conflicted with a
given To and From range.

Were you using it differently?

If not, could you provide an example
where it fails?

It would sure help me.

Thanks,

Gary Walter

 

[John Spencer (MVP)]

Sorry for the long delay, been out of town with a sick child (at 33 years, he is
still my child).

Dates are stored by Access/Jet as a double number with the date being the number
of days since December 30, 1899. Times are stored as the decimal portion of the
number - so .25 = 6:00 AM. Unfortunately doubles are not totally accurate and
therefore the time can be off very slightly (in the milliseconds area). Times
without dates are stored as 0.nnnnn and therefore unless formatted can display
as 12/30/1899.