Date split in Mysql based on rate info in table

Question

The system is a hotel management software with multiple hotels attached to it. The schema is as follows:

CREATE TABLE `ms_property` (
  `id` int(10) NOT NULL,
  `name` varchar(254) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ms_property` (`id`, `name`) VALUES(1, 'Black Forest');   
CREATE TABLE `ms_property_room` (  `id` int(10) NOT NULL,  `property_id` int(10) NOT NULL,
  `room_name` varchar(254)  NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ms_property_room` (`id`, `property_id`, `room_name`) VALUES (1, 1, 'Standard Room'),
(2, 1, 'AC Room');

CREATE TABLE `ms_tariff_type` (
  `tt_id` bigint(20) NOT NULL, 
  `tt_tariff_name` text 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `ms_tariff_type` (`tt_id`,`tt_tariff_name`) VALUES
(1, 'Season Rates'),
(2, 'Contracted Rates');

CREATE TABLE `room_tariff` (
  `id` bigint(20) NOT NULL ,
  `room_id` bigint(20) ,
  `tariff_type_id` bigint(20) ,
  `tariff_from` date,
  `tariff_to` date,  
  `single_rate` int(11),
  `default_rate` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `room_tariff` (`id`, `room_id`,`tariff_type_id`,`tariff_from`, `tariff_to`, `single_rate`, `default_rate`) VALUES
(1, 1, 1, '2019-01-01', '2019-01-20',1000,2000),
(2, 1, 2, '2019-02-06', '2019-02-12',5000,10000),
(3, 2, 1, '2019-03-05', '2019-04-10',8000,7000);

CREATE TABLE `tariff_hike_day` (
  `id` bigint(20) NOT NULL,
  `room_id` bigint(20) ,
  `tariff_type_id` bigint(20) ,
  `hd_tariff_from` date,
  `hd_tariff_to` date,
  `hd_single_rate` int(11),
  `hd_default_rate` int(11),
  `thd_sunday` smallint(6) COMMENT 'Is rate applicable on Sunday 1=>yes 0=>no',
  `thd_monday` smallint(6) COMMENT 'Is rate applicable on Monday 1=>yes 0=>no',
  `thd_thuesday` smallint(6) COMMENT 'Is rate applicable on Tuesday 1=>yes 0=>no',
  `thd_wednesday` smallint(6) COMMENT 'Is rate applicable on Wednesday 1=>yes 0=>no',
  `thd_thursday` smallint(6) COMMENT 'Is rate applicable on Thursday 1=>yes 0=>no',
  `thd_friday` smallint(6) COMMENT 'Is rate applicable on Friday 1=>yes 0=>no',
  `thd_saturday` smallint(6) COMMENT 'Is rate applicable on Saturday 1=>yes 0=>no'  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tariff_hike_day` (`id`, `room_id`, `tariff_type_id`,`hd_tariff_from`, `hd_tariff_to`, `hd_single_rate`, `hd_default_rate`, `thd_sunday`, `thd_monday`, `thd_thuesday`, `thd_wednesday`, `thd_thursday`, `thd_friday`, `thd_saturday`) VALUES
(1, 1, 1, '2019-01-05', '2019-01-10',100,200, 1, 1, 1, 1, 1, 1, 1),
(1, 2, 1, '2019-03-09', '2019-03-25',400,600, 1, 0, 0, 1, 0, 0, 0);

The scenario is to display the room rates applicable to hotels based on rate info provided in two tables. Normally a room will have different types of rate like "Contracted Rates", "Seasonal Rates" etc and in each type, Hotel Administrative Team will provide the applicable rates and the date range in which the rates are applicable.
The problem arises when the Hotel Administrative Team wants to specify additional hikes which are applicable on certain days. This information is stored in tariff_hike_day table where the Hotel Administrative Team can specify the date range and the days (sunday, monday etc) on which the hike is to be applied on base rate.
When the full entry is completed, the system is expected to display the result as follows:

+-------+---------------+---------------+------------------+------------+------------+-------------+--------------+    
| Sl No | Property Name |     Room      |   Tariff Type    | Date From  |  Date To   | Single Rate | Default Rate |    
+-------+---------------+---------------+------------------+------------+------------+-------------+--------------+    
|     1 | Black Forest  | Standard Room | Season Rates     | 2019-01-01 | 2019-01-04 |        1000 |         2000 |    
|     2 | Black Forest  | Standard Room | Season Rates     | 2019-01-05 | 2019-01-10 |        1100 |         2200 |    
|     3 | Black Forest  | Standard Room | Season Rates     | 2019-01-11 | 2019-01-20 |        1000 |         2000 |    
|     4 | Black Forest  | Standard Room | Contracted Rates | 2019-02-06 | 2019-02-12 |        5000 |        10000 |    
|     5 | Black Forest  | AC Room       | Season Rates     | 2019-03-05 | 2019-03-09 |        8000 |         7000 |    
|     6 | Black Forest  | AC Room       | Season Rates     | 2019-03-10 | 2019-03-10 |        8400 |         8600 |    
|     7 | Black Forest  | AC Room       | Season Rates     | 2019-03-11 | 2019-03-12 |        8000 |         7000 |    
|     8 | Black Forest  | AC Room       | Season Rates     | 2019-03-13 | 2019-03-13 |        8400 |         8600 |    
|     9 | Black Forest  | AC Room       | Season Rates     | 2019-03-14 | 2019-03-16 |        8000 |         7000 |    
|    10 | Black Forest  | AC Room       | Season Rates     | 2019-03-17 | 2019-03-17 |        8400 |         8600 |    
|    11 | Black Forest  | AC Room       | Season Rates     | 2019-03-18 | 2019-03-19 |        8000 |         7000 |    
|    12 | Black Forest  | AC Room       | Season Rates     | 2019-03-20 | 2019-03-20 |        8400 |         8600 |    
|    13 | Black Forest  | AC Room       | Season Rates     | 2019-03-21 | 2019-03-23 |        8000 |         7000 |    
|    14 | Black Forest  | AC Room       | Season Rates     | 2019-03-24 | 2019-03-24 |        8400 |         8600 |    
|    15 | Black Forest  | AC Room       | Season Rates     | 2019-03-25 | 2019-04-10 |        8000 |         7000 |    
+-------+---------------+---------------+------------------+------------+------------+-------------+--------------+

Any help would be appreciated.

I tried using calendar table @ https://gist.github.com/bryhal/4129042 for generating a base table and further tried to join to it using between clause. But so far the split part is not coming out well. — Black Rider, Jun 19 '19 at 08:02

Mohammedshafeek C S · Answer 1 · 2019-07-13T15:04:19.767

I know bit late to answer but hope it would help you.

First of all you need to make sure that there is no overlapped dates for same room with same tariff on both tables 'room_tariff' and 'tariff_hike_day'

To find it you can use the queries given below.

Finding Duplicate Dates(Overlapped dates) in room_tariff Table

SELECT 
  a.* 
FROM
  `room_tariff` AS a 
  INNER JOIN `room_tariff` AS b 
    ON a.`id` != b.`id` 
    AND a.`room_id` = b.`room_id` 
    AND a.`tariff_type_id` = b.`tariff_type_id` 
    AND NOT (
      (
        a.`tariff_from` > b.`tariff_from` 
        AND a.`tariff_from` > b.`tariff_to`
      ) 
      OR (
        a.`tariff_to` < b.`tariff_from` 
        AND a.`tariff_to` < b.`tariff_to`
      )
    ) 
GROUP BY a.`room_id`,
  a.`tariff_type_id`,
  a.`tariff_from`,
  a.`tariff_to` 
ORDER BY a.`room_id` ASC,
  a.`tariff_type_id` ASC,
  a.`tariff_from` ASC ;

Finding Duplicate Dates(Overlapped dates) in tariff_hike_day Table

SELECT 
  a.* 
FROM
  `tariff_hike_day` AS a 
  INNER JOIN `tariff_hike_day` AS b 
    ON a.`id` != b.`id` 
    AND a.`room_id` = b.`room_id` 
    AND a.`tariff_type_id` = b.`tariff_type_id` 
    AND NOT (
      (
        a.`hd_tariff_from` > b.`hd_tariff_from` 
        AND a.`hd_tariff_from` > b.`hd_tariff_to`
      ) 
      OR (
        a.`hd_tariff_to` < b.`hd_tariff_from` 
        AND a.`hd_tariff_to` < b.`hd_tariff_to`
      )
    ) 
GROUP BY a.`room_id`,
  a.`tariff_type_id`,
  a.`hd_tariff_from`,
  a.`hd_tariff_to` 
ORDER BY a.`room_id` ASC,
  a.`tariff_type_id` ASC,
  a.`hd_tariff_from` ASC ;

Both queries should return 'ZERO' rows to avoid over lapping. Here i joined same table and checking overlapped dates for same room with same tariff.

This link will help you get more explanation

To get result as you expected, We can do with the help of Stored Procedure as follows.

DELIMITER $$

DROP PROCEDURE IF EXISTS `testprocedure`$$

CREATE PROCEDURE `testprocedure`()
BEGIN
  DECLARE my_id,
  my_room_id,
  my_tariff_type_id,
  my_hd_id BIGINT ;
  DECLARE my_single_rate,
  my_default_rate,
  my_hd_single_rate,
  my_hd_default_rate INT ;
  DECLARE my_tariff_from,
  my_tariff_to,
  my_hd_tariff_from,
  my_hd_tariff_to,
  currentdate,
  startdate,
  stopdate DATE ;
  DECLARE my_thd_sunday,
  my_thd_monday,
  my_thd_tuesday,
  my_thd_wednesday,
  my_thd_thursday,
  my_thd_friday,
  my_thd_saturday SMALLINT ;

  DECLARE cur_done INTEGER DEFAULT 0 ;
  DECLARE `should_rollback` BOOL DEFAULT FALSE;
  DECLARE cur1 CURSOR FOR 
  SELECT 
    a1.*,
    a2.id,
    hd_tariff_from,
    hd_tariff_to,
    hd_single_rate,
    hd_default_rate,
    thd_sunday,
    thd_monday,
    thd_thuesday,
    thd_wednesday,
    thd_thursday,
    thd_friday,
    thd_saturday 
  FROM
  `room_tariff` AS a1 
  LEFT JOIN `tariff_hike_day` a2 
    ON a1.`room_id` = a2.`room_id` 
    AND a1.`tariff_type_id` = a2.`tariff_type_id` 
    AND a2.`hd_tariff_from` != '0000-00-00' 
    AND NOT (
      a1.`tariff_from` > a2.`hd_tariff_to` 
      OR a1.`tariff_to` < a2.`hd_tariff_from`
    )
    WHERE a1.tariff_from != '0000-00-00' 
  AND a1.`tariff_from` <= a1.`tariff_to`
  ORDER BY a1.`room_id` ASC,
  a1.`tariff_type_id` ASC,
  a1.`tariff_from` ASC,
  a2.`hd_tariff_from` ASC ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_done = 1 ;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `should_rollback` = TRUE;


  START TRANSACTION;     
  CREATE TABLE IF NOT EXISTS `room_rate_temp` (
    `id` INT (11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `room_id` BIGINT (20) NOT NULL,
    `tariff_type_id` BIGINT (20) NOT NULL,
    `tariff_from` DATE NOT NULL,
    `tariff_to` DATE NOT NULL,
    `single_rate` INT (11) NOT NULL,
    `default_rate` INT (11) NOT NULL,
    `resultset_id` INT (11) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE = INNODB DEFAULT CHARSET = utf8 ;

  SET @last_res_id := 0 ;
  TRUNCATE TABLE room_rate_temp ;
  OPEN cur1 ;
  loop_matched_tables :
  LOOP    
    FETCH cur1 INTO my_id,
    my_room_id,
    my_tariff_type_id,
    my_tariff_from,
    my_tariff_to,
    my_single_rate,
    my_default_rate,
    my_hd_id,
    my_hd_tariff_from,
    my_hd_tariff_to,
    my_hd_single_rate,
    my_hd_default_rate,
    my_thd_sunday,
    my_thd_monday,
    my_thd_tuesday,
    my_thd_wednesday,
    my_thd_thursday,
    my_thd_friday,
    my_thd_saturday ;

    IF cur_done = 1 THEN 
    CLOSE cur1 ;
    LEAVE loop_matched_tables ;
    END IF ;

    IF my_tariff_from <= my_tariff_to THEN

        IF @last_res_id = my_id THEN
        SELECT id,tariff_from FROM `room_rate_temp` WHERE `resultset_id` = my_id ORDER BY id DESC LIMIT 1 INTO @lastid,@last_tariff_from ;
        SET my_tariff_from := @last_tariff_from ;
        DELETE FROM room_rate_temp WHERE id = @lastid ; 
        END IF ;    

        IF my_hd_id IS NULL THEN        
        INSERT INTO room_rate_temp 
        VALUES
          (
        NULL,
        my_room_id,
        my_tariff_type_id,
        my_tariff_from,
        my_tariff_to,
        my_single_rate,
        my_default_rate,
        my_id
          ) ;       
        ELSE
        IF ( my_hd_tariff_from <= my_hd_tariff_to ) THEN        
            SET startdate := my_tariff_from ;
            SET currentdate := my_tariff_from ;
            SET stopdate := my_tariff_to ;
            SET @insflag := 1 ;         
            SET @last_insid := @last_hike_flag := @hiketablecovered := @splitonce := 0 ;            

            WHILE
              currentdate <= stopdate DO 
              SET @my_repeat_col_name := DAYNAME(currentdate) ;
              SET @hd_single_rate := my_single_rate ;
              SET @hd_default_rate := my_default_rate ;

              SELECT 
            CASE
              @my_repeat_col_name 
              WHEN 'Sunday' 
              THEN my_thd_sunday 
              WHEN 'Monday' 
              THEN my_thd_monday 
              WHEN 'Tuesday' 
              THEN my_thd_tuesday 
              WHEN 'Wednesday' 
              THEN my_thd_wednesday 
              WHEN 'Thursday' 
              THEN my_thd_thursday 
              WHEN 'Friday' 
              THEN my_thd_friday 
              WHEN 'Saturday' 
              THEN my_thd_saturday 
              ELSE NULL 
            END AS mydate INTO @hikeapplicable ;

              IF ( currentdate BETWEEN my_hd_tariff_from AND my_hd_tariff_to ) THEN

                IF ( @last_hike_flag != @hikeapplicable ) THEN
                    SET @insflag := 1 ;
                    SET @last_hike_flag := @hikeapplicable ;
                    SET @splitonce := 1 ;
                    IF ( @hikeapplicable = 1 ) THEN
                        SET @hd_single_rate := my_single_rate + my_hd_single_rate ;
                        SET @hd_default_rate := my_default_rate + my_hd_default_rate ;
                    END IF ;        
                END IF ;                
                SET @hiketablecovered := 1;

               ELSEIF ( (currentdate > my_hd_tariff_to) AND ( @hiketablecovered = 1 ) AND (@splitonce = 1) ) THEN       
               IF(@last_hike_flag = 1) THEN 
               SET @insflag := 1;
               END IF ;
               SET @hiketablecovered := @splitonce := 0 ;               
               END IF ;

               IF (@insflag = 1) THEN               
                INSERT INTO room_rate_temp VALUES ( NULL, my_room_id, my_tariff_type_id, currentdate, currentdate, @hd_single_rate, @hd_default_rate, my_id );
                SET @last_insid := LAST_INSERT_ID() ;   
                SET @insflag := 0 ;            
               ELSE
                UPDATE room_rate_temp SET tariff_to = currentdate WHERE id = @last_insid;               
               END IF ;

               SET currentdate = ADDDATE(currentdate, INTERVAL 1 DAY) ;

            END WHILE ;

        END IF ;

        END IF ;

    SET @last_res_id := my_id;  

    END IF ;

  END LOOP loop_matched_tables ;

  SET @count:=0;

  SELECT (@count:=@count+1) AS `Sl No`, d.name AS `Property Name`, c.room_name AS Room, b.tt_tariff_name AS `Tariff Type`, a.tariff_from AS `Date From`, a.tariff_to AS `Date To`, a.single_rate AS `Single Rate`, a.default_rate AS `Default Rate`  
  FROM room_rate_temp AS a INNER JOIN ms_tariff_type AS b ON a.tariff_type_id = b.tt_id INNER JOIN ms_property_room AS C
  ON a.room_id = c.id INNER JOIN ms_property AS d ON c.property_id = d.id;

  IF `should_rollback` THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;

END$$

DELIMITER ;

In this procedure,

For storing the result, I created one temp table and will exist until next query so that you can fetch last result at any time.

First i joined tariff and hike table to find the matching for similar date range.

Then looping the query result and breaks rows when hike is applicable.

Date split in Mysql based on rate info in table

1 Answers1