Hello. There is such a database: http://sqlfiddle.com/#!9/9554b . The first table is responsible for displaying hotel rooms that exist, the second for existing hotel bookings. I tried to visualize it: image For this order of arrangement / distribution of numbers meets the sql code. He takes new dates of arrival and departure, compares them with the main table and checks the available number for these dates. The first available free number he assigns to new armor. Everything is logical. From here and it follows such a "chaotic" distribution of numbers. Here, actually, itself select request for issue of 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, no one will live in the hotel on the 12th, 13th, 14th ... 20th day. These are free days. They were formed due to the distribution of dates by my script. But if the 11th number comes with a reservation for dates from 12 to 20, then the script will reject it (return null), because the check-in / check-out dates overlap. And this is quite expected, but I would like to solve this problem. If you redistribute the reservation, then everything will fall into place, and the number from 12 to 20 will open. image I did not touch the existing armor, I moved only future ones. I would like to know how real this idea is with redistribution / defragmentation, and how to implement it (maybe there was already something similar, or is there such a sql query for my case)?

  • Yes, like two fingers. The reservation does not fix a specific number, but any of the numbers in this class. Respectively think and add request correct sorting. But redistribution is not a task for the SQL, but let the client chug over it. - Akina

3 answers 3

Thanks for the interesting question.

I tried to implement the algorithm in MS SQL, and ignored filtering by number type in most places, I hope you can translate it into code for MySQL and add where you need conditions for the type - it should be easy.

The basic meaning of the algorithm is to reset the numbers of all with armor, whose beginning is more than the current date, then re-assign them numbers one by one, distributing them as closely as possible to the dates already occupied.

 declare @start date = '2016-12-12'; -- Новая дата заезда declare @end date = '2016-12-20'; -- Новая дата выезда declare @today date = '2016-12-11'; update main set numbernomer = null where datestart > @today; declare @mainnumber int; while exists (select * from main where numbernomer is null) begin select top(1) @mainnumber = id from main where numbernomer is null order by datestart asc, datediff(day, datestart, dateend) desc; update m0 set numbernomer = m1.nomer from main m0 cross apply (SELECT top 1 a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, m0.dateend) * DATEDIFF(day, m.dateend, m0.datestart) <= 0 left join (select m2.numbernomer, max(m2.dateend) as dateend from main m2 group by m2.numbernomer) as m2 on a.nomer = m2.numbernomer WHERE a.type = 'lux' AND m.numbernomer IS NULL order by datediff(day, m2.dateend, m0.datestart), a.nomer ) as m1 where m0.id = @mainnumber; end; SELECT a.nomer FROM allnomer a LEFT JOIN main m ON a.nomer = m.numbernomer AND DATEDIFF(day, m.datestart, @end) * DATEDIFF(day, m.dateend, @start) <= 0 WHERE a.type = 'lux' AND m.numbernomer IS NULL;
  • Wow Thanks, of course, but you didn’t come across an article on how to translate ms sql to mysql? I have been sitting for an hour and everything is unsuccessful ... - firebear
  • 1) replace top 1 in select with limit 1 after select 2) remove the first "day" parameter in the Datediff function 3) replace declare with set, removing the data type 4) take the piece that joins via cross apply and put it directly into assignment of value. I would do it all myself, but I have no database at hand, but SQLFiddle does not work for some reason. - minamoto
  • In short, it all came to the conclusion that your answer is the most correct one. I put ms sql, created a database there and executed the query. And oh, a miracle, everything worked as it should. But I could not translate it into my language. That this is the only right answer is my fault. Not quite correctly, I asked my question and only you understood me. - firebear

Optimizing query from the discharge of magic variables. It is designed for a more correct database structure, in which, in the main table, there is no namenomer field since it is duplicated from the allnomer table than it violates the second normal form . On your database structure, the query works and it can be further reduced, due to the fact that the number type will be taken directly from the main (but I do not recommend it, but I recommend normalizing the database structure).

 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 id=@cid) 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<=@today 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

Test select subquery on sqlfiddle.com (In the first column of the result is a new number that will be set to reserve records, after it all fields of the reserve record, including the old number. As a result, there are no records with nomer = 302, because as a result of optimization this number turns out to be free from 12 to 20 and not a single reserve has been assigned to it).

Principle of operation: The deepest subquery receives a list of all numbers with the last date active for today ( @today variable) reserve. If the number is currently free, then the last busy date is considered to be yesterday. Each number is glued to all existing reserves with later dates from all numbers of this type. But in fact, their data are not used in the work, they are only needed to generate a known number of records sufficient to emulate recursion. The resulting records are sorted in reverse order by the end date of the current reserve. Those. The first number to be processed is 304, because its current reserve ends on the 13th.

For each record, we are trying to find such a reserve record that has not been previously encountered in the request (it is not in the @used variable) and whose start date is closest to the current processed date. If the record is found ( @cid NOT NULL), then we @cid take its end date as the next working date. Thus, in the next record, one record will be found with the beginning closest to the current one. When the processing of the current number ends (the new number is not equal to the old @cnum ), the working date is reset to the release date of the new number and we are looking for the next chain of reserves.

PS The stored procedure can of course be made simpler and more understandable, while preserving the general principle. But it is interesting to me to do it with a single request, especially in such a situation when it seems impossible.

  • Thanks for the decision, but I found a mistake in it. If you specify a different date younger than 10, then the dates are defragmented is not true. I created a new question, where I attached screenshots of what is happening ru.stackoverflow.com/questions/604503/… - firebear

Structures and content - from the in-post to sqlfiddle.

 CREATE PROCEDURE pack() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE d_start DATE; DECLARE d_end DATE; DECLARE num varchar(7); DECLARE cur CURSOR FOR SELECT datestart, dateend FROM main ORDER BY 1, 2 DESC; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; CREATE TEMPORARY TABLE appts(nomer varchar (7), free_from DATE) ENGINE = Memory; INSERT INTO appts(nomer, free_from) SELECT nomer, 0 FROM allnomer; CREATE TEMPORARY TABLE shedule(nomer varchar (7), datestart DATE, dateend DATE) ENGINE = Memory; OPEN cur; read_loop: LOOP FETCH cur INTO d_start, d_end; IF done THEN LEAVE read_loop; END IF; SELECT a.nomer INTO num FROM appts a WHERE a.free_from < d_start ORDER BY 1 LIMIT 1; INSERT INTO shedule SELECT num, d_start, d_end; UPDATE appts a SET a.free_from = d_end WHERE a.nomer = num; END LOOP read_loop; CLOSE cur; SELECT nomer, datestart, dateend FROM shedule ORDER BY 1, 2; DROP TEMPORARY TABLE shedule; DROP TEMPORARY TABLE appts; END;