3s41hLxV

1SET @start = '2016-12-12'; -- Новая дата заезда
2SET @end = '2016-12-20'; -- Новая дата выезда
3SELECT a.nomer
4FROM allnomer a
5LEFT JOIN main m
6  ON a.nomer = m.numbernomer
7  AND DATEDIFF(m.datestart, @end) * DATEDIFF(m.dateend, @start) <= 0
8WHERE a.type = 'lux' AND m.numbernomer IS NULL
9LIMIT 1
10	
11declare @start date = '2016-12-12'; -- Новая дата заезда
12declare @end date = '2016-12-20'; -- Новая дата выезда
13declare @today date = '2016-12-11';
14
15update  main 
16set     numbernomer = null
17where   datestart > @today;
18
19declare @mainnumber int;
20
21while exists (select * from main where numbernomer is null)
22begin
23
24    select top(1) @mainnumber = id
25    from    main
26    where   numbernomer is null
27    order by datestart asc, datediff(day, datestart, dateend) desc;
28
29    update m0
30    set     numbernomer = m1.nomer
31    from    main m0
32            cross apply (SELECT top 1 a.nomer
33                        FROM    allnomer a
34                                LEFT JOIN main m
35                                ON a.nomer = m.numbernomer
36                                AND DATEDIFF(day, m.datestart, m0.dateend) * DATEDIFF(day, m.dateend, m0.datestart) <= 0
37                                left join (select m2.numbernomer, max(m2.dateend) as dateend from main m2 group by m2.numbernomer) as m2
38                                        on a.nomer = m2.numbernomer
39                        WHERE a.type = 'lux' AND m.numbernomer IS NULL
40                        order by datediff(day, m2.dateend, m0.datestart), a.nomer
41                        ) as m1
42    where   m0.id = @mainnumber;
43
44end;
45
46SELECT a.nomer
47FROM allnomer a
48LEFT JOIN main m
49  ON a.nomer = m.numbernomer
50  AND DATEDIFF(day, m.datestart, @end) * DATEDIFF(day, m.dateend, @start) <= 0
51WHERE a.type = 'lux' AND m.numbernomer IS NULL;
52	
53CREATE PROCEDURE pack()
54BEGIN
55    DECLARE done INT DEFAULT FALSE;
56    DECLARE d_start DATE;
57    DECLARE d_end DATE;
58    DECLARE num varchar(7);
59    DECLARE cur CURSOR FOR SELECT datestart, dateend FROM main ORDER BY 1, 2 DESC;
60    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
61    CREATE TEMPORARY TABLE appts(nomer varchar (7), free_from DATE) ENGINE = Memory;
62    INSERT INTO appts(nomer, free_from) SELECT nomer, 0 FROM allnomer;
63    CREATE TEMPORARY TABLE shedule(nomer varchar (7), datestart DATE, dateend DATE) ENGINE = Memory;
64    OPEN cur;
65read_loop:
66    LOOP    
67        FETCH cur INTO d_start, d_end;
68        IF done THEN
69            LEAVE read_loop;
70        END IF; 
71        SELECT a.nomer INTO num 
72            FROM appts a
73            WHERE a.free_from < d_start 
74            ORDER BY 1 
75            LIMIT 1;    
76        INSERT INTO shedule 
77            SELECT num, d_start, d_end;
78        UPDATE appts a
79            SET a.free_from = d_end
80            WHERE a.nomer = num;
81    END LOOP read_loop;
82    CLOSE cur;
83    SELECT nomer, datestart, dateend 
84        FROM shedule
85        ORDER BY 1, 2;
86    DROP TEMPORARY TABLE shedule;
87    DROP TEMPORARY TABLE appts;
88END;