[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help modifying sql query to find units available for reservation between 2 given dates when the same unit number may be contained in several records.

Posted on 2005-05-10
10
Medium Priority
?
201 Views
Last Modified: 2010-04-23
Greetings:

Give a table similar in to the following:

|resID | unitID | resArrival | resDeparture|
-------------------------------------------
|1       | A1     |5/11/05    | 5/13/05    |
|2       | A1     |5/1/05      | 5/3/05     |
|3       | A2     |5/15/05    | 5/18/05    |

Where resID is the primary key and unitID is a foreign key.

I was assisted with the following SQL statement to search for Vacancy between 2 given dates:

Dim vacancySearchSQL As String = "SELECT * FROM tblReservations WHERE (resArrivalDate > " & "#" & dtpSrchStartDate.Text & "#" & " and resArrivalDate > " & "#" & dtpSrchEndDate.Text & "#" & ")" & " or " & "(resDepartureDate < " & "#" & dtpSrchStartDate.Text & "#" & " and resDepartureDate < " & "#" & dtpSrchEndDate.Text & "#" & ")"

This works fine when there are no units repeated in column unitID. However, the table may contain any number of repetitions and in this case a unit is still returned as available where resArrival and resDeparture don’t overlap the search dates.

For example is I search for units available on 5/11/05/ and 5/13/05 the first record will not be returned but the second one will be since it displays a reservation that took place before the current search dates.

What I would like to do is to discard any results with a unitID that has already been determined to be unavailable at a given date. Is there any way to modify the sql statement above, or do I need to take a different approach to solving problem? Your help is greatly appreciated.

0
Comment
Question by:isrxl
  • 5
  • 4
10 Comments
 
LVL 10

Expert Comment

by:NetworkArchitek
ID: 13974110
Yes, this approach won't work, it is a good attempt though. You need a "NOT EXISTS" sub query. Though, I'm not following something, so you want to to give a StartDate and an EndDate and you want to find records which do not conflict with those dates? I just want to make sure but you will certainly need to use a "NOT EXISTS" or "NOT IN" subquery to accomplish this.
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13974161
Basically the query should look something like this...I guessed at the table names and you'll have to take care of creating the vacancySearchSQL value yourself but the logic should work.  Essentially you want a list of all units that don't have a reservation overlapping the search criteria.

SELECT DISTINCT UnitID
FROM tblUnit
INNER JOIN tblReservation
     ON tblUnit.UnitID = tblReservation.UnitID
WHERE (tblReservation.resArrival < dtpSrchStartDate AND tblReservation.resDeparture < dtpSrchStartDate)
     OR (tblReservation.resArrival > dtpSrchEndDate AND tblReservation.resDeparture > dtpSrchEndDate)
0
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 total points
ID: 13974172
correction...

SELECT DISTINCT UnitID
FROM tblUnit
LEFT OUTER JOIN tblReservation
     ON tblUnit.UnitID = tblReservation.UnitID
WHERE (tblReservation.resArrival < dtpSrchStartDate AND tblReservation.resDeparture < dtpSrchStartDate)
     OR (tblReservation.resArrival > dtpSrchEndDate AND tblReservation.resDeparture > dtpSrchEndDate)
     OR tblReservation.UnitID IS NULL

the earlier query would have omitted units without any reservations.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13974176
the UnitID in the SELECT clause should be mapped to the tblUnit table
0
 

Author Comment

by:isrxl
ID: 13975169
RE: NetworkArchitek
"so you want to to give a StartDate and an EndDate and you want to find records which do not conflict with those dates?"
That is correct

RE: BriCrowe
This seems right on the money, however, for some reason, it returns all units even if the provided start and end dates are identical to the start and end dates of an existing record. Any ideas?? Here's the customized  statment:

Dim srchSQL As String = "SELECT DISTINCT units.uniID FROM units " & _
"LEFT OUTER JOIN reservations ON units.uniID = reservations.uniID " & _
"WHERE(reservations.resArrival < " & "#" & dtpSrchStartDate.Text & "#" & " And reservations.resDeparture < " & "#" & dtpSrchStartDate.Text & "#" & ")" & _
"OR (reservations.resArrival > " & "#" & dtpSrchEndDate.Text & "#" & " AND reservations.resDeparture > " & "#" & dtpSrchEndDate.Text & "#" & ")" & _
"OR reservations.uniID IS NULL"

Could I have done something wrong here? Thank you for your help.

0
 

Author Comment

by:isrxl
ID: 13977430
I'm using an Access data base, could that be the problem why the the statement is not working properly?
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13977830
is it supposed to be "uniID" or "unitID"?  I would recommed creating the query in Access first to verify it's accuracy (just hard code the dtp values) and then transfer it to your app.
0
 

Author Comment

by:isrxl
ID: 13978069
Yes "uniID" is the actual name. From what I tested so far, it seems that if all existing reserved dates fall within the 2 search dates it will not return that particular unit. However if only 1 reservation dates stored conflicts witht the search date it will still return that unit. Will try to do what you're recommending. Thank you.
0
 

Author Comment

by:isrxl
ID: 13978969
Seems like Access uses a different approach to produce LEFT OUTER JOIN. I'm sure BriCrowe's answer is correct given a different dbs environment so I'll accept your answer and post the query undear Access to see if any one can help modify it to work with Access. Thank you.
0
 

Author Comment

by:isrxl
ID: 13993516
NetworkArchitek was right about using NOT IN. Here is the solution that works exactly as intended:

select u2.uniid from units u2 where u2.uniID not in(
select distinct u.uniid
from units u left join reservations r on u.uniid = r.uniid
where @sdate <= resDeparture and @edate >=resarrival)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question