Форум пользователей MySQL

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;