dynamically update table and re- arrange records

mysql

51 просмотра

2 ответа

I have a table lets say:

**tblHotel**
    id
    start_date
    end_date
    rate

Now I want to write procedure for update records for date range, say for example I have data:

id   start_date     end_date     rate
1    2016/01/01     2016/01/10   10
2    2016/01/11     2016/01/20   50 

Now if a new date range and rate comes from supplier I want to update tables record like new range is.

  start_date   end_date     rate
  2016/01/05   2016/01/12   100

Now updated records should be like this:

id   start_date     end_date     rate
1    2016/01/01     2016/01/04   10
2    2016/01/05     2016/01/12   100
3    2016/01/13     2016/01/20   50 

Query

insert into tbl_Hotel(start_date, end_date, rate)
        select $start_date, $end_date, $rate
        from dual
        where not exists (select 1
                          from tbl_Hotel h
                          where h.start_date <= $end_date and h.end_date >= $start_date
                         );
Автор: Juned Ansari Источник Размещён: 08.11.2019 11:08

Ответы (2)


1 плюс

Решение

This might not be exactly right - and/or it may be a fraction too contrived - but I'm sure it's along the right lines...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,start_date DATE NOT NULL UNIQUE
,end_date DATE NOT NULL
,rate INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'2016-01-01','2016-01-10',10),
(2,'2016-01-11','2016-01-20',50); 

INSERT INTO my_table
SELECT y.id
     , COALESCE(GREATEST(x.start_date,y.start_date),x.start_date) start_date
     , COALESCE(LEAST(x.end_date,y.end_date),x.end_date) end_date
     , x.rate
  FROM 
     ( SELECT a.start_date
            , MIN(COALESCE(b.start_date,a.end_date)) end_date
            , a.rate
         FROM 
            ( SELECT LEAST(start_date,'2016-01-05') start_date, LEAST(end_date,'2016-01-04') end_date, rate FROM my_table
              UNION
              SELECT GREATEST(start_date,'2016-01-13') start_date, GREATEST(end_date,'2016-01-12') end_date, rate FROM my_table
              UNION
              SELECT '2016-01-05','2016-01-12',100
            ) a
         LEFT 
         JOIN
            ( SELECT LEAST(start_date,'2016-01-05') start_date, LEAST(end_date,'2016-01-04') end_date,rate FROM my_table
              UNION
              SELECT GREATEST(start_date,'2016-01-13') start_date, GREATEST(end_date,'2016-01-12') end_date,rate FROM my_table
              UNION
              SELECT '2016-01-05','2016-01-12',100
            ) b
           ON b.start_date < a.end_date
          AND b.end_date > a.start_date
          AND a.start_date < b.start_date
          AND b.rate <> a.rate
        GROUP
           BY a.start_date
            , a.rate
       HAVING end_date >= start_date
     ) x
  LEFT
  JOIN my_table y
    ON y.start_date < x.end_date
   AND y.end_date > x.start_date
   AND y.rate = x.rate
    ON DUPLICATE KEY UPDATE start_date = COALESCE(GREATEST(x.start_date,y.start_date),x.start_date) 
                          , end_date = COALESCE(LEAST(x.end_date,y.end_date),x.end_date); 

SELECT * FROM my_table;
+----+------------+------------+------+
| id | start_date | end_date   | rate |
+----+------------+------------+------+
|  1 | 2016-01-01 | 2016-01-04 |   10 |
|  2 | 2016-01-13 | 2016-01-20 |   50 |
|  3 | 2016-01-05 | 2016-01-12 |  100 |
+----+------------+------------+------+

Note that the ids don't shift to suit the amended dates. That's deliberate.

Автор: Strawberry Размещён: 20.08.2016 12:31

0 плюса

First check if the update is possible without breaking consistency.

Then run the following statements in one transaction:

-- define new entry data
set @start_date := '2016-01-05';
set @end_date   := '2016-01-12';
set @rate := 100;

-- find where to insert the new row
select h.id into @new_id
from tbl_Hotel h
where h.start_date > @start_date;

-- make space for the new row
update tbl_Hotel h
set h.id = h.id + 1
where h.id >= @new_id;

-- find the row that will be before the new one
select max(h.id) into @prev_id
from tbl_Hotel h
where h.start_date < @start_date;

-- find the row that will be after the new one
select min(h.id) into @next_id
from tbl_Hotel h
where h.start_date > @start_date;

-- update the end date of the previous row
update tbl_Hotel h
set h.end_date = @start_date - interval 1 day
where h.id = @prev_id;

-- update the start date of the following row
update tbl_Hotel h
set h.start_date = @end_date + interval 1 day
where h.id = @next_id;

-- insert the new row
insert into tbl_Hotel (`id`, `start_date`, `end_date`, `rate`)
values (@new_id, @start_date, @end_date, @rate);

sqlfiddle

Автор: Paul Spiegel Размещён: 20.08.2016 11:37
Вопросы из категории :
32x32