Retrieve total available days in date range compared against multiple records

Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates?

| ID | code  | date_arrival | date_departure
| 4  | APT01 | 2015-04-10   | 2015-04-15
| 5  | APT01 | 2015-04-22   | 2015-04-29
| 6  | APT02 | 2015-04-11   | 2015-04-19
| 8  | APT02 | 2015-04-20   | 2015-04-25

For example: The user enters 2015-04-16 as their start date and 2015-04-28 as their end date. The available days over this period are 16th - 21st using APT01, and 26th - 27th using APT02

This means there are 8 available days out of those requested by the user. How can I query the data and get 8 as my result?

First solution

Well, I tried a solution. It works but it is pretty ugly. But it works...

SELECT count(*)
FROM (
    SELECT code, dates.selected_date
    FROM appartments
    INNER JOIN (select * from 
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates
    WHERE (code, selected_date) NOT IN (
        SELECT code, dates.selected_date
        FROM appartments
        INNER JOIN (select * from 
          (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
           (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
          ) dates ON dates.selected_date between date_arrival and date_departure)
    GROUP BY code, dates.selected_date) available_dates_by_code

Change the date period in the line WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28') dates.

Remove the first level SELECT FROM to get all dates of unoccupied apartments for dates between '2015-04-16' AND '2015-04-28'.

You may want to change the '2015-01-01' dates to something earlier (ie. CURDATE() if you're only working with future dates). This query will only return next 30 years dates past '2015-01-01', so change it to something like CURDATE() - '1 YEAR'

I'm very curious to see if someone have a better solution...

How it works

From the bottom to the top :

• The first SELECT gets all occupied dates for all appartments.

• The second SELECT gets all dates wanted and removes all appartment/date couples that are occupied.

• The third select count the number of appartement/date couple available between the provided dates.

Second solution

SELECT
  (
    SELECT adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date
    FROM apartments
    INNER JOIN (SELECT * FROM 
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) -
  (
    SELECT count (code, dates.selected_date)
    FROM apartments
    INNER JOIN (SELECT * FROM
      (select adddate('2015-01-01',t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
       (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3) v
      ) dates ON dates.selected_date between date_arrival and date_departure
    WHERE selected_date BETWEEN '2015-04-16' AND '2015-04-28'
  ) AS 'days_of_availability'

This one is much more simpler. The second SELECT counts the number of days existing for the two dates multiplied by the number of apartments. The third SELECT counts the number of occupied days for all the apartments. The top

StackExchange Database Administrators Q#98553, answer score: 4