CODEWITHSUNDEEP
sql
Charles B Hamlyn
Charles B Hamlyn

Reputation: 85

SQL Selecting records where one date range doesn't intersect another

I'm trying to write a simple reservation program for a campground.

I have a table for campsites (one record for every site available at the campground).
I have a table for visitors which uses the campsite table's id as a foreign key, along with a check in date and check out date.

What I need to do is gather a potential check in and check out date from the user and then gather all the campsites that are NOT being used at any point in that range of dates.

I think I'm close to the solution but there's one piece I seem to be missing.

I'm using 2 queries. 1) Gather all the campsites that are occupied during that date range. 2) Gather all campsites that are not in query 1.

This is my first query:

SELECT Visitors.CampsiteID, Visitors.CheckInDate, Visitors.CheckOutDate
FROM Visitors
WHERE (((Visitors.CheckInDate)>=#CHECKINDATE# 
    And (Visitors.CheckInDate)<=#CHECKOUTDATE#) 
    Or ((Visitors.CheckOutDate)>=#CHECKINDATE# 
    And (Visitors.CheckOutDate)<=CHECKOUTDATE));

I think I'm missing something. If the #CHECKINDATE# and #CHECKOUTDATE# both occur between someone else's Check-in and Check-out dates, then this doesn't catch it.

I know I could split this between two queries, where one is dealing with just the #CHECKINDATE# and the second is dealing with the #CHECKOUTDATE#, but I figure there's a cleaner way to do this and I'm just not coming up with it.

This is my second one, which I think is fine the way it is:

SELECT DISTINCT Campsites.ID, qryCampS_NotAvailable.CampsiteID
FROM Campsites LEFT JOIN qryCampS_NotAvailable 
ON Campsites.ID = qryCampS_NotAvailable.CampsiteID
WHERE (((qryCampS_NotAvailable.CampsiteID) Is Null));

Thanks, Charles

Upvotes: 0

Views: 169

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

To get records that overlap with the requested time period, use this simple logic. Two time periods overlap when one starts before the other ends and the other ends after the first starts:

SELECT v.CampsiteID, v.CheckInDate, v.CheckOutDate
FROM Visitors v
WHERE v.CheckInDate <= #CHECKOUTDATE# and
      v.CheckOutDate >= #CHECKINDATE# ;

Upvotes: 1

Related Questions