How to defragment dates in a database?

Question

PHP

Hello. There is such a database: sqlfiddle.com/#!9/9554b . The first table is responsible for displaying the rooms in the hotel that exist, the second - for the existing reservations in the hotel. I tried to visualize it:

The sql code is responsible for this order of placement / distribution of numbers. It takes the new check-in and check-out dates, compares them to the main table, and checks if a room is available for those dates. He will assign the first available number to the new armor. Everything is logical. From here follows such a "chaotic" distribution of numbers. Here, in fact, is the select request for issuing a number.

SET @start = '2016-12-12'; -- Новая дата заезда
SET @end = '2016-12-20'; -- Новая дата выезда
SELECT a.nomer
FROM allnomer a
LEFT JOIN main m
  ON a.nomer = m.numbernomer
  AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0
WHERE a.type = 'lux' AND m.numbernomer IS NULL
LIMIT 1

But I have one problem. In fact, on the 12th, 13th, 14th ... on the 20th, no one will live in the hotel. These are free days. They were formed due to the distribution of dates by my script. But if a booking arrives on the 11th for dates from 12 to 20, then the script will reject it (return null), because the check-in/out dates overlap. And this is quite expected, but I would like to solve this problem. If you redistribute the reservations, then everything will fall into place, and the number from 12 to 20 will open.

I didn't touch existing armors, I moved only future ones.
I would like to know how real this undertaking with redistribution / defragmentation is, and how to bring it to life (maybe there was already something like this, or is there such a sql query for my case)?

Answer 1

IMHO all your problem is in the holes - the remaining free day. Add a condition to the first script so that not the first available one is selected, but the room whose check-out date coincides with the "new arrival date -1". Although in reality the hotel seems to have a check-in time and a check-out time for this. The dates of entry and exit must be the same.