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.