Why are reservations defragmented incorrectly on a different date?

Question

MySQL

Hello. I am trying to defragment my database. It looks like this: 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. If you visualize it, then this miracle looks like this:
To defragment means to arrange the armor (highlighted in color is the armor) so that they are as close to each other as possible, that is, like this:

And this code copes with my task:

set @today:=date('2016-12-11');
update main M
  join (
   select cid, nomer
     from (
       select A.nomer,
              @cstart:=if(@cnum=A.nomer,@cstart,A.start),
              @cnum:=A.nomer,
              @cid:=(select M.id
                       from main M,allnomer MT
                      where MT.nomer=M.numbernomer and MT.type='lux'
                        and M.datestart>@cstart
                        and find_in_set(M.id,@used)=0
                      order by datestart
                      limit 1
                    ) cid,
              @cstart:=(select dateend from main where [email protected]) dend,
              @used:=coalesce(concat(@used,',',@cid),@used)
         from (
          select A.nomer, A.start
            from (
              select A.nomer,
                     (select coalesce(min(dateend),@today-interval 1 day)
                        from main M
                       where datestart<[email protected] and M.numbernomer=A.nomer) start
                from allnomer A where type='lux'
            ) A,
            main M, allnomer MT
           where MT.nomer=M.numbernomer and MT.type='lux' and M.datestart>A.start
           order by A.start desc, A.nomer
         ) A,
         (select @cid:=0,@cnum:=0,@cstart:=NULL,@used:='') Y
     ) X where cid is not null
  ) U
  on M.id=U.cid
 set M.numbernomer=U.nomer

But if you change set @today:=date('2016-12-11'); on set @today:=date('2016-12-05'); , then the database will look like this:
Why does the defragmentation occur incorrectly when the date changes? Tell me how to fix it?

Answer 1

Why are reservations defragmented incorrectly on a different date?

Divide the day into hours and build your download schedule.