Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

available booking time query 2

Status
Not open for further replies.

mthompo

Technical User
Jul 28, 2006
98
GB
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...

 
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]
 
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...

Code:
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"

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

Code:
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
Code:
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?
 
for anyone who is interested
this works great -thanks to Bill K
and thanks to guelphdad

Code:
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

Code:
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 &"'"
 
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
 
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

Code:
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
 
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
 
many thanks

added () around date clause'

Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top