Finding a calculated value by sql date range?

Question

MySQL

Hello everybody!
Conventionally, there is a small hotel with rooms for 1,2,3 beds with the possibility of sharing. Also it would be desirable to organize search in a range of dates in places, but not in numbers. It is in the settlement that a snag arises.
The bottom line: for example, there is an application for a 3-bed room for 1 place from June 10 to 15. And then there is an application for 2 places from the 17th to the 20th, let's say. This means that, for example, in the period from June 9 to 21, there will always be 1 seat in this issue. And when searching in the range of June 9-21, this number should be found. At the same time, if applications intersect (for example, an application for 2 places not from the 17th, but from the 14th), this means that on at least one day of the period 9-21 the number will be fully occupied and will not be shown during the search.
I have not figured out how to write a query that would take into account these details. There is a table with a list of rooms and capacity, as well as a table with applications (where the application number, room number, number of people are indicated). Initially I tried a query like this:

SELECT rooms.id, rooms.size - SUM(bid.quantity) AS freeplaces from rooms,bid
    where bid.room_id = rooms.id
    and ( DATE(bid.date_start) BETWEEN DATE('2018-06-9') AND DATE('2018-06-21')
          OR DATE(bid.date_end) BETWEEN DATE('2018-06-9') AND DATE('2018-06-21') )
GROUP by bid.room_id having freeplaces >= 1

But the problem is that if applications for 1 and 2 people do not intersect in a given period, then 1 place will be free at any time of the period, and the query will calculate the sum of people for applications in this period (1 + 2 = 3) and say that there are no available rooms. Although if the applications do not intersect, then the maximum number of people in the applications should be searched, and if they intersect, then sum up.
I don’t know how to organize all this within one or at least several requests with nested ones. I will be grateful for any hint.

Answer 1

It won't work with a date range. It is necessary to count in the cycle for each day separately. Create a new table room_quantity (room_id, quantity, date) which is recalculated by a trigger (

update room_quantity set quantity = quantity + new.quantity where date between new.date_start and new.date_end

) when changing the bid. And look for the availability of free beds on this table