CODEWITHSUNDEEP
mysqlsql
Juned Ansari
Juned Ansari

Reputation: 5283

Stored procedure for update date range and price in mysql

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

Upvotes: 1

Views: 360

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I'm not going to write the code for you, but handling overlapping time frame is tricky. You need to handle this as different cases:

If nothing overlaps, then this is simple:

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
                     );

Easy . . . And in the stored procedure the where can be handled using if logic.

Then the hard part. There are four types of overlaps:

   -------hhhhhhhhhhh--------
a) ---------xxxxx------------
b) -----xxxxxx---------------
c) ----------xxxxxx----------
d) --xxxxxxxxxxxxxxxxxxxxxx--

And, then it gets a bit more complicated because a new rate period could overlap with more than one existing period.

Arrrg! How do you approach this? Carefully and with test cases. You might even want to use a cursor (although there are non-cursor-based methods as well).

The idea is to pull out one overlapping existing period. Then, for that period handle the logic:

a) The existing period needs to be split into two parts (with appropriate end dates. Then the new reservation can just be added.

b) The start date of the existing period has to change to one more than the end date of the new one. Then the new one inserted.

c) The end date of the existing period has to change to one less than the start date of the new one. Then the new one inserted.

d) The old record is removed and the new one inserted.

As I say, good tests for your stored procedure are important, so you can actually check that it works.

Upvotes: 2

Related Questions