473,320 Members | 1,817 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

{SOLVED} Availability date ranges, using one query

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.Checkout is DateTime field

Labelled queryAvailableRooms
Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout date] DateTime;
  2.  
  3. SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  4.  
  5. FROM Booking
  6.  
  7. WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
  8.  
  9. Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
  10.  
  11. Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1));
  12.  
The second query uses the first query (as Reserved Rooms)

Expand|Select|Wrap|Line Numbers
  1. SELECT Rooms.RoomNum
  2. FROM Rooms LEFT JOIN queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
  3. WHERE (((queryAvailableRooms.Room) Is Null));
  4.  
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
Nov 13 '06 #1
9 5992
NeoPa
32,556 Expert Mod 16PB
I hope this is what you mean...
Expand|Select|Wrap|Line Numbers
  1. SELECT BlahBlah, 
  2. FROM BlahBlah JOIN (other sub query) AS subQName ON BlahBlah.Fld1=subQName.Fld1
  3. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT subQuery.*
  2. FROM (SELECT *
  3. FROM [tblAccounts]) AS subQuery
This would be equivalent to
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM [tblAccounts]
Using subqueries like this has the benefit that you have access to the whole of the SQL - therefore you have greater control.
Nov 13 '06 #2
Hi Thanks for your reply

I think I sort've already knew that part.

I tried the following code

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Rooms.RoomNum
  3. FROM Rooms LEFT JOIN ( 
  4.  
  5. SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  6.  
  7. FROM Booking
  8.  
  9. WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) 
  10.  
  11. Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) 
  12.  
  13. Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1)) 
  14.  
  15. )
  16.  
  17. AS queryAvailableRooms ON Rooms.RoomNum=queryAvailableRooms.Room
  18.  
  19.  
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
Nov 13 '06 #3
Hello again,

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Rooms.RoomNum
  3. FROM Rooms LEFT JOIN (SELECT Booking.Room, Booking.Arrival, Booking.Checkout
  4.  
  5. FROM Booking
  6.  
  7. WHERE (((Booking.Arrival) Between #9/2/1999# And #9/6/1999#-1)) 
  8.  
  9. Or ((([Checkout]-1) Between #9/2/1999# And #9/6/1999#)) 
  10.  
  11. Or (((Booking.Arrival)<#9/2/1999#) And (([Checkout]-1)>#9/6/1999#-1)) 
  12.  
  13. ) AS queryAvailableRooms ON Rooms.RoomNum = queryAvailableRooms.Room WHERE queryAvailableRooms.Room is null
  14.  
  15.  
  16.  
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??!?!
Nov 13 '06 #4
tell a lie..........


I've amended my query so that it is like so:
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarBooking.CarID, CarBooking.datefrom,CarBooking.dateto
  4.  
  5. FROM CarBooking 
  6.  
  7. WHERE (((CarBooking.datefrom) Between #12/3/2005# And #12/4/2005#)) 
  8.  
  9. Or ((([CarBooking.dateto]) Between #12/3/2005# And #12/4/2005#)) 
  10.  
  11. Or (((CarBooking.datefrom)<#12/3/2005#) And (([CarBooking.dateto])>#12/4/2005#)) 
  12.  
  13. ) AS queryAvailableCars ON Cars.CarID = queryAvailableCars.CarID
  14. WHERE queryAvailableCars.CarID is null;
  15.  
  16.  
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?!?
Nov 13 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarID, datefrom, dateto
  4. FROM CarBooking 
  5. WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
  6. Or (dateto Between #12/3/2005# And #12/4/2005#) 
  7. Or (datefrom<#12/3/2005# And dateto>#12/4/2005#)) AS queryAvailableCars 
  8. ON Cars.CarID = queryAvailableCars.CarID
  9. WHERE queryAvailableCars.CarID is null;
  10.  
  11.  
Alternatively you could try:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT CarID FROM Cars
  3. WHERE CarID NOT IN (SELECT CarID FROM CarBooking 
  4. WHERE (datefrom Between #12/3/2005# And #12/4/2005#) 
  5. Or (dateto Between #12/3/2005# And #12/4/2005#) 
  6. Or (datefrom<#12/3/2005# And dateto>#12/4/2005#));
  7.  
  8.  
Nov 13 '06 #6
NeoPa
32,556 Expert Mod 16PB
Try :-
Expand|Select|Wrap|Line Numbers
  1. SELECT CarID
  2. FROM Cars LEFT JOIN (SELECT CarID
  3. FROM CarBooking
  4. WHERE ((datefrom<[Please enter checkout date]) 
  5.   AND (dateto>[Please enter arrival date]))) AS subCarBooking
  6. ON Cars.CarID=subCarBooking.CarID
  7. WHERE subCarBooking.CarID IS NOT Null;
Nov 13 '06 #7
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Cars.CarID
  3. FROM Cars LEFT JOIN (SELECT CarID
  4. FROM CarBooking
  5. WHERE ((datefrom<[Please enter checkout date]) 
  6.   AND (dateto>[Please enter arrival date]))) AS subCarBooking
  7. ON Cars.CarID=subCarBooking.CarID
  8. WHERE subCarBooking.CarID IS Null;
  9.  
  10.  
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!
Nov 14 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
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#))));
Nov 14 '06 #9
NeoPa
32,556 Expert Mod 16PB
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.
Nov 14 '06 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: Dave Robinson | last post by:
I was wondering if anyone could help me with a problem I'm having. I've been using Dreamweaver to create a hotel booking system for a friend of mine, using MySQL (version 4.0.21) and PHP 5. The...
7
by: Thomas R. Hummel | last post by:
Hello all, I am trying to write a query that compares a member's enrollment period with the products that their group has had during that period (all members belong to a group and the products...
17
by: Lapchien | last post by:
My table has a record called date/time - filled (not surprisingly) with a date and time. I can format the date in one query field, also the time, using format in an expression. My user wants me...
1
by: Dalan | last post by:
I have attempted to resolve a problem regarding erroneous output using a Between And parameter on several Access 97 queries, but to no avail. The queries are used for report output and...
12
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date...
67
by: PC Datasheet | last post by:
Transaction data is given with date ranges: Beginning End 4/1/06 4/4/06 4/7/06 4/11/06 4/14/06 4/17/06 4/18/06 4/21/06 426/06 ...
1
by: Matt | last post by:
Hi all, I have a database with a table storing a list of names, invoice dates, and invoice amounts. What I'm looking to do is to create a sum of the invouice amounts based on a range of...
22
by: boliches | last post by:
I am trying to get a crosstab query (in access 2000) to group data by date range. TRANSFORM Sum(tblInvoice.InvBalance) AS SumOfInvBalance SELECT tblInvoice.DealerID, Sum(tblInvoice.InvBalance) AS...
16
by: Alex30093 | last post by:
OK This is my 1st post to any forum, but I rely on forums all the time. Short story is I find myself needing to become a Access DBA for work. Situation: I want to use one table of events that...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.