{SOLVED} Availability date ranges, using one query

[mharrison]

Hello,

I am developing a small java web-based car-pool booking system app which interacts with an access database. I am trying to write 2 queries: The first which will specify whether a given car is available on a given date range e.g. from: 1/12/05 to 12/12/05. the second which will run if the first query is unsuccessful e.g. a list of other cars available on the chosen dates. I have been looking at a Microsoft page which I believe may help do what I require but there are 2 separate queries in their example. I would prefer to have one using either a sub query or join, as I don't know of a way of providing Parameters into the second query using SQL, and its quite messy as well! The 2 queries they have given are below (This is based on a hotel reservation system but functionality is the same)

Booking.Arrival is DateTime field
Booking.Checkou t is DateTime field

Labelled queryAvailableR ooms

PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout date] DateTime;
 
SELECT Booking.Room, Booking.Arrival, Booking.Checkout
 
FROM Booking
 
WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
 
Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
 
Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1));
 

The second query uses the first query (as Reserved Rooms)

SELECT Rooms.RoomNum
FROM Rooms LEFT JOIN queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
WHERE (((queryAvailableRooms.Room) Is Null));
 

Ideally I want to know how these 2 queries can be joined into one so that a web form can pass the dates into it as a connection string.

Any help would be greatly appreciated.

Many Thanks

Mark

[NeoPa]

I hope this is what you mean...

SELECT BlahBlah, 
FROM BlahBlah JOIN (other sub query) AS subQName ON BlahBlah.Fld1=subQName.Fld1
etc

Basically, you surround the SELECT subquery in parentheses () and name it (using AS name) then it becomes like any other data source.
A basic, but real life example would be :

SELECT subQuery.*
FROM (SELECT *
FROM [tblAccounts]) AS subQuery

This would be equivalent to

SELECT *
FROM [tblAccounts]

Using subqueries like this has the benefit that you have access to the whole of the SQL - therefore you have greater control.

[mharrison]

Hi Thanks for your reply

I think I sort've already knew that part.

I tried the following code

 
SELECT Rooms.RoomNum
FROM Rooms LEFT JOIN ( 
 
SELECT Booking.Room, Booking.Arrival, Booking.Checkout
 
FROM Booking
 
WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
 
Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
 
Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1)) 
 
)
 
AS queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
 
 

and access just moaned as it does about the expression being too difficult to work out. I've tried to space out the query so it looks ok. I've also left the parameters in for now.

Any extra help, always appreciated

mark

[mharrison]

Hello again,

I think by a stroke of luck and some more playing, it was simple.

 
SELECT Rooms.RoomNum
FROM Rooms LEFT JOIN (SELECT Booking.Room, Booking.Arrival, Booking.Checkout
 
FROM Booking
 
WHERE (((Booking.Arrival) Between #9/2/1999# And #9/6/1999#-1)) 
 
Or ((([Checkout]-1) Between #9/2/1999# And #9/6/1999#)) 
 
Or (((Booking.Arrival)<#9/2/1999#) And (([Checkout]-1)>#9/6/1999#-1)) 
 
) AS queryAvailableRooms ON Rooms.RoomNum = queryAvailableRooms.Room WHERE queryAvailableRooms.Room is null
 
 
 

I've done the above query and it seems to work. Ignore the specifics of the dates. They return the correct two rooms that they should! Now I have a framework, to add some car information.... ....

Thanks again, oh and how shall i close this post??!?!

[mharrison]

tell a lie..........


I've amended my query so that it is like so:

 
SELECT Cars.CarID
FROM Cars LEFT JOIN (SELECT CarBooking.CarID, CarBooking.datefrom,CarBooking.dateto
 
FROM CarBooking 
 
WHERE (((CarBooking.datefrom) Between #12/3/2005# And #12/4/2005#)) 
 
Or ((([CarBooking.dateto]) Between #12/3/2005# And #12/4/2005#)) 
 
Or (((CarBooking.datefrom)<#12/3/2005#) And (([CarBooking.dateto])>#12/4/2005#)) 
 
) AS queryAvailableCars ON Cars.CarID = queryAvailableCars.CarID
WHERE queryAvailableCars.CarID is null;
 
 

I have the following booking table:

Booking ID, Car ID, date from, date to
1 , 1, 12/01/2005, 15/05/2005
2 , 2, 14/01/2005, 15/02/2005
3 , 3, 13/01/2005, 31/01/2006

I would assume that the only Car to be available is car 2, since its date range is not within the 2 specified in the query,

However the query returns 1 and 2.

do you have any ideas anyone?!?

[MMcCarthy]

I'm pretty sure the problem lies in the bracketing. I've simplified the query down to the basis requirements. If you paste it into the sql query window in Access the Jet engine will impose further brackets but that's not a problem. Try it and see what result you get.

 
SELECT Cars.CarID
FROM Cars LEFT JOIN (SELECT CarID, datefrom, dateto
FROM CarBooking 
WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
Or (dateto Between #12/3/2005# And #12/4/2005#) 
Or (datefrom<#12/3/2005# And dateto>#12/4/2005#)) AS queryAvailableCars 
ON Cars.CarID = queryAvailableCars.CarID
WHERE queryAvailableCars.CarID is null;
 
 

Alternatively you could try:

 
SELECT CarID FROM Cars
WHERE CarID NOT IN (SELECT CarID FROM CarBooking 
WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
Or (dateto Between #12/3/2005# And #12/4/2005#) 
Or (datefrom<#12/3/2005# And dateto>#12/4/2005#));
 
 

[NeoPa]

Try :-

SELECT CarID
FROM Cars LEFT JOIN (SELECT CarID
FROM CarBooking
WHERE ((datefrom<[Please enter checkout date]) 
  AND (dateto>[Please enter arrival date]))) AS subCarBooking
ON Cars.CarID=subCarBooking.CarID
WHERE subCarBooking.CarID IS NOT Null;

[mharrison]

Hello guys,

The first two attempts at the sql posted by mmccarthy came up with the same issue. that is, Car 1 and car 2 were selected. using neo's code, came up with cars 1 and 3. note that only car two should appear. this got me thinking that the query need only be changed slightly. I tried this:

 
SELECT Cars.CarID
FROM Cars LEFT JOIN (SELECT CarID
FROM CarBooking
WHERE ((datefrom<[Please enter checkout date]) 
  AND (dateto>[Please enter arrival date]))) AS subCarBooking
ON Cars.CarID=subCarBooking.CarID
WHERE subCarBooking.CarID IS Null;
 
 

and this worked! I will have to try out other dates for testing and will let you know how it goes.

thanks guys, I'm not a complete noob to sql (having had 1 yrs experience) but this was largely in oracle 9. access doesn't like the same syntax ive found!

[MMcCarthy]

Good catch on the logic:


This one should also work

SELECT CarID FROM Cars
WHERE CarID NOT IN (SELECT CarID FROM CarBooking
WHERE (((datefrom Between #12/3/2005# And #12/4/2005#)
And (dateto Between #12/3/2005# And #12/4/2005#))
Or ((datefrom<#12/3/2005# And dateto>=#12/3/2005#)
Or (datefrom<#12/4/2005# And dateto>=#12/4/2005#))));

[NeoPa]

Thanks guys, I'm not a complete noob to SQL (having had 1 yrs experience) but this was largely in oracle 9. Access doesn't like the same syntax I've found!

No you're not.
I've just reviewed my SQL and noticed two mistakes - both of which you've fixed in your version.
The second mistake was particularly stupid, as it would give the equivalent of an INNER JOIN - Doh!

Well done.

If no-one has any objections, I'll mark this thread as solved.