MySQL — максимальное значение минимального значения за период

Моя проблема:

Допустим, мне нужно найти наилучшую возможность (максимальное выделение, которое может предложить предмет), в определенный период — 10 дней для этого примера.
Пользователь хочет уйти в отпуск на 5 дней.

Пример набора результатов из текущего запроса показывает распределение за день:

+---------+------------+-----------+
| Item Id |    Date    | Allotment |
+---------+------------+-----------+
|   10    | 20-10-2018 |    100    |
|   10    | 21-10-2018 |    80     |
|   10    | 22-10-2018 |    100    |
|   10    | 23-10-2018 |    100    |
|   10    | 24-10-2018 |    100    |
|   10    | 25-10-2018 |    100    |
|   10    | 26-10-2018 |    100    |
|   10    | 27-10-2018 |    70     |
|   10    | 28-10-2018 |    100    |
|   10    | 29-10-2018 |    100    |
+---------+------------+-----------+

Набор результатов в приведенном выше примере выполняется с помощью следующего запроса:

foreach($arrivalDates as $key => $date) {
$arrivalDate = $date['from'];
$departureDate = $date['till'];

$allotmentSQLArr[] = "EXISTS (SELECT 1 FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN $arrivalDate AND $departureDate AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration)";
$whereSqlArr[] = "(
EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $arrivalDate AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $departureDate AND ea.departure_possible = 1)
)";
}

$query = "SELECT
t.fkItemId,
t.arrival_date,
SUM(t.total_allotment) as total_allotment
FROM (
SELECT
ir.fkItemId,
ir.id,
ea.arrival_date,
(ea.allotment * r.max_occupancy) as total_allotment
FROM
item_room ir
INNER JOIN
room r
ON
r.id = ir.fkRoomId
INNER JOIN
availability as ea
ON
ea.fkItemRoom = ir.id
AND
ea.arrival_date BETWEEN :begin_date AND :end_date
AND
ea.allotment > 0
AND
(".implode(' OR ', $whereSqlArr).")
WHERE
(".implode(' OR ', $allotmentSQLArr).")
) as T
GROUP BY
t.fkItemId, t.arrival_date
";

Решение, которое я ищу:
Теперь мне нужно знать, может ли предмет предложить пользователю пребывание в течение 5 дней, и доступно ли по меньшей мере 80 выделений в день.
В последующие периоды с 20-10-2018 по 24-10-2018, с 21-10-2018 по 25-10-2018, с 22-10-2018 по 26-10-2018 максимум 80. За период с 23-10-2018 по 27-10-2018 максимум 70.

В этом случае элемент должен быть доступен, поскольку он может предложить пользователю выделение как минимум за один период.

Мой собственный подход:

SELECT
a.id
FROM
tl_item a
WHERE
EXISTS (
SELECT
1
FROM (
SELECT
t.fkItemId,
t.arrival_date,
SUM(t.total_allotment) as total_allotment
FROM (
SELECT
ir.fkItemId,
ir.id,
ea.arrival_date,
(ea.allotment * r.max_occupancy) as total_allotment
FROM
tl_item_room ir
INNER JOIN
tl_room r
ON
r.id = ir.fkRoomId
INNER JOIN
tl_et_availability as ea
ON
ea.fkItemRoom = ir.id
AND
ea.arrival_date BETWEEN :begin_date AND :end_date
AND
ea.allotment > 0
AND
((
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539853200 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540458000 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539939600 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540544400 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540026000 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540630800 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540112400 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540720800 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540198800 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540807200 AND ea.departure_possible = 1)
))
AND
(EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539853200 AND 1540458000 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539939600 AND 1540544400 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540026000 AND 1540630800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540112400 AND 1540720800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540198800 AND 1540807200 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration))
) as T
GROUP BY
t.fkItemId,t.arrival_date
)  as B
WHERE
((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment > :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment > :allotment))
AND
b.fkItemId = a.id

)

Я знаю, что приведенное выше вернет TRUE, если только ОДНА запись соответствует критериям. Что мне нужно сделать, так это то, что следующая строка проверяет, имеют ли ВСЕ записи между этими датами выделение больше назначенного:

WHERE ((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment >= :allotment))