Retrieve total available days in date range compared against multiple records
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.