3

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?

4

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 SELECT does (number of days) minus (number of occupied days).

Fun fact: it took me almost 30 minutes to get this query working. That's a shame.

  • Hey @DeadEye, I've been playing around with this a bit more recently and have come across a bit of an issue which I wonder if you can help me with. The query you wrote is awesome but doesn't seem to account for the same availability over multiple apartments. For example, the query in your answer returns 9 for my sample data in the question which is great but if I change the apartment code in row 2 (ID 5) to APT03 it returns 22 instead. I think this is because it's returning all available days for all apartments rather than the amount of days available between the 2 user supplied dates – Andy May 7 '15 at 14:19
  • After many hours of thoughts, I tried a new way to get the correct query. I edited my answer with a new query. Can you test it ? (; – DeadEye May 8 '15 at 10:58
  • Thanks, but when I try to run it now I get this error: #1060 - Duplicate column name 'i'? – Andy May 8 '15 at 12:49
  • Can you try to rename the columns i to j in the second part of the query ? – DeadEye May 8 '15 at 13:58
  • It still returns the same error. I tried changing each i in the first select to i1, i2, i3 and i4 to no avail :( – Andy May 8 '15 at 14:17
4

This was a fun exercise :) I decided to throw this problem on it's head and instead ask, which days are not available first then get which days are available. You will need a "days" table that list all days. There are a lot of scripts out there to find how to do this dynamically. test is your table in the question and days is this days table.

First, get just the days being requested from the the date table and duplicate for each apartment:

SELECT code, dy
INTO days_lookup
FROM 
(SELECT DISTINCT code
FROM test t
) t
INNER JOIN days d
ON d.dy BETWEEN '2015-Apr-16' AND '2015-Apr-28'

Then you want to find all the days that were occupied:

SELECT t.id, t.code, d.dy
INTO occupied
FROM test t
INNER JOIN days_lookup d
ON t.code = d.code
AND d.dy BETWEEN t.date_arrival and t.date_departure

Finally, join back to days_lookup table to find any days that were not occupied.

SELECT dl.*
FROM days_lookup dl
LEFT JOIN occupied o
ON dl.code = o.code
and dl.dy = o.dy
WHERE o.code IS NULL
  • Thanks for taking the time to come up with this answer Chris. I decided to accept DeadEye's answer because it avoids the need to create another table – Andy Apr 22 '15 at 16:01
  • 1
    I agree, that was a fun exercise (; – DeadEye Apr 22 '15 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.