available booking time query 2

[mthompo]

hi need help with this query,
have a list of times for a booking system
i want a query that lists times that are available for a
certain date.
It needs to look at the bookings table and not display
times that have a booking related to them

tables:
times
timeid time
1 9:00
2 10:00
3 11:00
4 12:00...

bookings
bookid timeid cusid date
1 2 1 28/07/06
2 3 2 28/07/06
3 1 5 29/07/06
4 2 7 29/07/06...

 

[TonyGroves]

Have you investigated LEFT (or RIGHT) JOIN?

 

[mthompo]

thanks for your reply

this shows times that have been taken
how do i change it to times that havent been taken

sqlwrk = "SELECT times.timeid, times.time "_
& "FROM times "_
& "INNER JOIN bookings ON times.timeid = bookings.timeid "_
& "WHERE book.bookdate = "&[datesql]

 

[guelphdad]

This item,

http://www.guelphdad.wefixtech.co.uk/sqlhelp/roomreservations.html

might help you understand the concept and then you can apply it to your particular needs.

 

[mthompo]

hi - have tried your example and am having trouble
can you have a look at what im doing and see if
i am on the right track

courtcarbook
courtcarbookid carid bookst bookend
1 5 2006-08-21 2006-08-24
2 3 2006-08-22 2006-08-23
3 7 2006-08-21 2006-08-26...

booksql = "select courtcarbook.carid, courtcarbook.bookst, courtcarbook.bookend "_ 
& "from courtcarbook "_
& "where courtcarbook.carid =" & x_carid _
& " group by carid "_ 
& "having sum("_
& "case when ( "_
& "bookst < " & x_bookst _
& " AND bookend > " & x_bookend _
& ") then 0 else 1 end) = 0"

 

[mthompo]

hi am still having probs with this
can someone give me further details on how to make this work

booksql = "SELECT courtcarbook.courtcarbookid "_ 
& "FROM courtcarbook"_
& " WHERE courtcarbook.carid =" & x_carid _
& " GROUP BY courtcarbookid "_
& "having sum("_
& "case when ("_
& "bookst <'" & bookstsql _
& "' and bookend <'"& bookendsql _
& "' or bookst >'" & bookstsql _
& "' and bookend >'" & bookendsql _
& "') then 0 else 1 end) = 0"

which produces

SELECT courtcarbook.courtcarbookid FROM courtcarbook WHERE courtcarbook.carid =13 GROUP BY courtcarbookid having sum(case when (bookst <'20061009' and bookend <'20061013' or bookst >'20061009' and bookend >'20061013') then 0 else 1 end) = 0

what part of the query tells me if its free or not?
am using asp - do i need to output the rs and look at
courtcarbookid?

 

[mthompo]

for anyone who is interested
this works great -thanks to Bill K
and thanks to guelphdad

SELECT A.start, A.end 
FROM bookings AS A 
WHERE '$Bend' BETWEEN A.start AND A.end -- True in case 1 and 4 
OR '$Bstart' BETWEEN A.start AND A.end -- True in case 2 and 4 
OR A.start BETWEEN '$Bstart' AND '$Bend' -- True in case 3

put into my instance

booksql = "SELECT courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend "_ 
& "FROM courtcarbook "_
& "WHERE '" & bookendsql & "' BETWEEN bookst AND bookend "  _
& "OR '" & bookstsql & "' BETWEEN bookst AND bookend " _
& "OR bookst BETWEEN '" & bookstsql & "' AND '" & bookendsql &"'"

 

[Baukep]

Hello,

I read this thread and because I'm having the same problems i thought I checkout the link guelphdad provided.

Got an error!

Where can I get the roomreservations.html please

Thanks in advance

Bauke

 

[mthompo]

just add 's' to html on link

http://www.guelphdad.wefixtech.co.uk/sqlhelp/roomreservations.shtml

never got this example to work - probably didnt spend enough time on it [used example above] but i did email
and got a detailed response - thanks again

 

[mthompo]

have the following sql that finds
booking spaces that are already booked
if the recordset is eof then the space is free

i want the query to not include bookings
that are the same as the booking being edited

SELECT courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend 
FROM courtcarbook
WHERE courtcarbook.carid =14  
AND '20061028' BETWEEN bookst AND bookend 
AND '20061028' BETWEEN bookst AND bookend 
OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028') 
OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028')
AND courtcarbook.courtcarbookid <> 34

however the bottom line says to not include courtcarbookid'
that are not equal to 34 - however if i run the query
it bring up a record whose id = 34!

can anyone tell me why it does this

 

[towerbase]

Because AND takes a higher precedence than OR

Use parenthensis to ensure that logical expressions are evaluated in exactly the order you want them.

Andrew
Hampshire, UK

 

[mthompo]

many thanks

added () around date clause'

SELECT courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend 
FROM courtcarbook
WHERE courtcarbook.carid =14  
AND ('20061028' BETWEEN bookst AND bookend 
AND '20061028' BETWEEN bookst AND bookend 
OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028') 
OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028'))
AND courtcarbook.courtcarbookid <> 34

 

[r937]

you can remove one of these --

OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028')
OR (courtcarbook.bookst <= '20061028') AND (courtcarbook.bookend >= '20061028')

r937.com | rudy.ca

 

[mthompo]

quite right - thanks to all