Hello everybody! Conventionally there is a small hotel with rooms for 1,2,3 places with the possibility of shares. And I want to organize a search by date range precisely by places, not by numbers. It is in the obsession there is a snag. The bottom line: for example, there is an application in a 3-bed room for 1 seat from 10 to 15 June. And then there is an application for 2 places from the 17th to the 20th. This means that, for example, in the period from June 9 to 21, there will always be 1 place in this number. And when searching in the range of June 9-21, this number should be found. Moreover, if the applications intersect (for example, an application for 2 places not from the 17th, but from the 14th), this means that at least one day of the 9-21 period will be fully occupied and will not be shown during the search.
I did not understand how I could write such a request, which 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 this query:
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 overlap in a given period, then 1 place will be free at any time of the period, and the request will calculate the amount of people on requests in a given period (1 + 2 = 3) and say that there is no room for this number. Although if the applications do not overlap, then they should look for the maximum number of people in the applications, and if they overlap, then they should already sum up. I don’t know how to organize all this within the framework of one or at least several requests with nested ones. I would be grateful for any tip.
