Your query has a logical flaw: it tries to find people who are NOT in the list of people who DO have coaching but only if the coaching is NOT in the target window.
Instead, join the employee and coaching table on employee_id, using all rows in employee but only using rows in coaching where the stop_date is later than or equal to the desired start date (coaching ends after the window starts) and the the start_date is less than or equal to the desired stop date (coaching starts before the window ends). Then exclude the employees with one or more matching rows in coaching.
SELECT e.id AS employee_id
FROM employee e
LEFT JOIN coaching c ON c.employee_id = e.id
AND c.end_date >= '2014-12-26'
AND c.start_date <= '2015-01-30'
WHERE c.employee_id IS NULL;
Live demo: http://sqlfiddle.com/#!9/79b39/4
From here, I modified the query slightly to get
select adddate('2015-02-01', numlist.id) as `my_date`,
weekday(adddate('2015-02-01', numlist.id)) as day_no,
dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');
which gives
+------------+--------+----------+
| my_date | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 | 0 | Monday |
| 2015-02-03 | 1 | Tuesday |
| 2015-02-09 | 0 | Monday |
| 2015-02-10 | 1 | Tuesday |
| 2015-02-16 | 0 | Monday |
| 2015-02-17 | 1 | Tuesday |
| 2015-02-23 | 0 | Monday |
| 2015-02-24 | 1 | Tuesday |
+------------+--------+----------+
8 rows in set (0.01 sec)
or from here
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 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 t1 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 t2 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 t3 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,
(select 0 t4 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) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');
result
+---------------+
| selected_date |
+---------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
+---------------+
8 rows in set (0.23 sec)
CREATE TABLE mydate( blah date);
INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
-- weekday(adddate('2015-02-01', numlist.id)) as day_no,
-- dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');
(need a table num)
mysql> select * from mydate;
+------------+
| blah |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |
OR
insert into mydate (blah)
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
(select 0 t0 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 t1 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 t2 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 t3 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,
(select 0 t4 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) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');
No table necessary. Same result as above.
Best Answer
First solution
Well, I tried a solution. It works but it is pretty ugly. But it works...
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 :
Second solution
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.