[FIXED] How to query available item leases based on a date range in MySQL?

Issue

We have a business that rents out international phone numbers to customers when traveling. When a customer makes an order We want to display to the customer the available phone numbers for his booking dates based on his start_date and end_date and numbers which is not occupied yet.

Since these phone numbers are rented out, I need to select from the table ONLY those numbers that are not rented out yet for dates that would interfere with the current customers dates.

I also don't want to rent out any phone number prior to 7 days after its end date. Meaning, If a customer booked a phone number for 1-1-2020 through 1-20-2020, I don't want this phone number to be booked by another customer before 1-27-2020. I want the phone number to have a 7 day window of being clear.

I have a table with the phone numbers and a table with the orders that is related to the phone numbers table via phone_number_id. The orders table has the current customers start_date and end_date for travel without the phone number id saved yet to it. The orders table also has the start_date and end_date for all other customers dates of travel as well as which phone_number_id was assigned/booked up for their travel dates.

How would the MySQL query look like when trying to select the phone numbers that are available for the current customers dates?

I build below query at the moment

SELECT x.id
     , x.area_code
     , x.phone_number
     , y.start_date
     , y.end_date 
  FROM vir_num_table x
  LEFT 
  JOIN orderitemsdetail_table y
    ON y.vn_id = x.id 
 WHERE y.start_date BETWEEN '2020-01-11' AND '2020-01-18' 
    OR y.start_date IS NULL

I've build this query but stuck here how can I add end_date logic.

Any help would be appreciated! Thanks in advance.

Solution

The way I'd approach the problem would be to look at conceptually, is as a cross product of the set of all phone numbers, along with the reservation timeframe, and then exclude those where there's a conflicting reservation.

A conflict would be an overlap, existing reservation that has a start_date before the end of the proposed reservation AND has an end_date on or after the start of the proposed reservation.

I'd do an anti-join pattern, something like this:

 SELECT pn.phone_number
   FROM phone_number pn

   LEFT
   JOIN reservation rs
     ON rs.phone_number  = pn.phone_number
    AND rs.start_dt     <= '2019-12-27' + INTERVAL +7 DAY
    AND rs.end_dt        > '2019-12-20' + INTERVAL -7 DAY
  WHERE rs.phone_number IS NULL 

That essentially says get all rows from phone number, along with matching rows from reservations (rows that overlap), but then exclude all the rows that had a match, leaving just phone_number rows that did not have a match.

We can make the < test a <= or , subtract 8 days, to tailor the "7 day" window before; we can tweak as we run the query through the test cases,

We can achieve an equivalent result using a NOT EXISTS and a correlated subquery. Some people find this easier to comprehend than the ant-join, but its essentially the same query, doing the same thing, get all rows from phone_number but exclude the rows where there is a matching (overlapping) row in reservation

 SELECT pn.phone_number
   FROM phone_number pn
  WHERE NOT EXISTS 
        ( SELECT 1 
            FROM reservation rs
           WHERE rs.phone_number  = pn.phone_number 
             AND rs.start_dt     <= '2019-12-27' + INTERVAL +7 DAY
             AND rs.end_dt        > '2019-12-20' + INTERVAL -7 DAY
        )

---

There are several questions on StackOverflow about checking for overlap, or no overlap, of date ranges.

See e.g.

How to check if two date ranges overlap in mysql?

PHP/SQL - How can I check if a date user input is in between an existing date range in my database?

MySQL query to select distinct rows based on date range overlapping

---

EDIT

Based on the SQL added as an edit to the question, I'd do the query like this:

SELECT pn.`id`
     , pn.`area_code`
     , pn.`phone_number`
  FROM `vir_num_table` pn
  LEFT
  JOIN `orderitemsdetail_table` rs
    ON rs.vn_id        = pn.id
   AND rs.start_date  <= '2020-01-18' + INTERVAL +7 DAY
   AND rs.end_date     > '2020-01-11' + INTERVAL -7 DAY
 WHERE rs.vn_id IS NULL

The two "tricky" parts. First is the anti-join, understanding how that works. (An outer join, to return all rows from vir_num_table but exclude any rows that have a matching row in reservations. The second tricky part is checking for the overlap, coming up with the conditions: r.start <= p.end AND r.end >= p.start, then tweaking whether we want to include the equals as an overlap, and tweaking the extra seven days (easiest to me to just subtract the 7 days from the beginning of the proposed reservation)

... now occurs to me like we need to add a guard period of 7 days on the end of the reservation period as well, doh!

Answered By - spencer7593

Answer Checked By - Clifford M. (PHPFixing Volunteer)