isrxl
asked on
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.
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.
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.
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.
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
OR (tblReservation.resArrival
SELECT DISTINCT UnitID
FROM tblUnit
INNER JOIN tblReservation
ON tblUnit.UnitID = tblReservation.UnitID
WHERE (tblReservation.resArrival
OR (tblReservation.resArrival
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the UnitID in the SELECT clause should be mapped to the tblUnit table
ASKER
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.resArr
"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.
"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.resArr
"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.
ASKER
I'm using an Access data base, could that be the problem why the the statement is not working properly?
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.
ASKER
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.
ASKER
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.
ASKER
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)
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)