Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question Unanswered: MySQL: Special Price Scenario

    I am working on a hotel booking engine. There is a special price scenario where the customer will see a special price based on the dates the admin defines.

    If the date the customer is booking a hotel room falls on the date range specified, the price they will see is what the admin has defined.

    So if the admin defines prices for a date rage from 1st Jan to 31st Jan in the following way,

    1st Jan 2011 to 15th Jan 2011 = $100 / per day
    16th Jan 2011 to 16th Jan 2011 = $500 / per day
    17th Jan 2011 to 31st Jan 2011 = $100 / per day

    Code:
    CREATE TABLE `special_prices` (
      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `date_start` DATE DEFAULT NULL,
      `date_end` DATE DEFAULT NULL,
      `special_price` DECIMAL(10,2) DEFAULT NULL,
      `status_id` TINYINT(4) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MYISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
    
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-1','2011-01-15','100','1');
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES ( NULL,'2011-01-16','2011-01-16','500','1');
    
    INSERT INTO `mydb`.`special_prices`(`id`,`date_start`,`date_end`,`special_price`,`status_id`) VALUES (NULL,'2011-01-17','2011-01-31','100','1');
    http://img832.imageshack.us/img832/4692/dml.png


    When I run the following query:
    Code:
    SELECT
      `id`,
      `date_start`,
      `date_end`,
      `special_price`
    FROM `special_prices`
    WHERE date_start >= '2011-01-12' AND date_end <= '2011-01-18'
    The result I get:
    Code:
    id	date_start	date_end	special_price	
    2	2011-01-16	2011-01-16	500.00
    Ideally the query should fetch all the 3 rows since they meet my query. Does anyone of you know why is this happening?


    Any help is appreciated.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Are you sure they meet the criteria? date_start >= '2011-01-12' only 2 meet that criteria and end_date <= '2011-01-18' meets the criteria of the previous subset of results. This looks good to me.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Mar 2008
    Posts
    36

    Question

    Quote Originally Posted by it-iss.com View Post
    Are you sure they meet the criteria? date_start >= '2011-01-12' only 2 meet that criteria and end_date <= '2011-01-18' meets the criteria of the previous subset of results. This looks good to me.
    Hi it-iss.com

    Thanks for the reply. Now I get all the three records. However, it does not meet what I am actually looking for.

    According the rates as mentioned above, can the query calculate the date difference and return the price accordingly?

    This is the query what I am running to achieve my goal:
    Code:
    SELECT
      `id`,
      `date_start`,
      `date_end`,
      `special_price`,
      `status_id`,
      (DATEDIFF('2011-01-18','2011-01-12')+1)*special_price AS DD
    FROM `special_prices`
    WHERE date_start <= '2011-01-18' AND date_end >= '2011-01-12'
    This is the result I get:
    Code:
        id  date_start  date_end    special_price  status_id     DD  
    ------  ----------  ----------  -------------  ---------  -------
         1  2011-01-01  2011-01-15         100.00          1   700.00
         2  2011-01-16  2011-01-16         500.00          1  3500.00
         3  2011-01-17  2011-01-31         100.00          1   700.00
    Where as the DD column should return the prices as

    $400 ($100 * 4 days, 12th to 15th)
    $500 ($500 *1 day, 16th to 16th)
    $200 ($100 * 2 days, 17th to 18th)

    Wondering if this can be done?


    Many thanks

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    try the following:

    Code:
    SELECT   `id`,
       `date_start`,
       `date_end`,
       `special_price`,
       `status_id`,
       (DATEDIFF(IF(date_end > '2011-01-18','2011-01-18',date_end),IF(date_start<'2011-01-12','2011-01-12',date_start))+1)*special_price AS DD
     FROM `special_prices`
     WHERE date_start <= '2011-01-18' AND date_end >= '2011-01-12';
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Mar 2008
    Posts
    36
    Quote Originally Posted by it-iss.com View Post
    Hi,

    try the following:

    Code:
    SELECT   `id`,
       `date_start`,
       `date_end`,
       `special_price`,
       `status_id`,
       (DATEDIFF(IF(date_end > '2011-01-18','2011-01-18',date_end),IF(date_start<'2011-01-12','2011-01-12',date_start))+1)*special_price AS DD
     FROM `special_prices`
     WHERE date_start <= '2011-01-18' AND date_end >= '2011-01-12';


    Thanks sir, that did the trick. Was I pretty close to your solution except that I did not use the if condition?

    Could you please explain why did you put the IF statements and what are they doing?

    Many thanks

  6. #6
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The promotion period is between the 12th to 18th of January. If you have a start date less than the 12th then we must use the 12th otherwise we use the start date. If the end date is over the 18th of January then we must use the 18th as the max date.

    So using your example if you the start date is the 1st of January and end date the 15th then start date is before the 12th so we must use the 12th. If the end date is past the 18th which it is not then we must use the 15th. The difference between the 15th and 12th is 3 + 1 is 4 times the special offer.

    The other solution involves the 17th to the 31st of January. The start date must be 17th with the end date the 18th (if end date > 18th then we use the 18th).
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  7. #7
    Join Date
    Mar 2008
    Posts
    36
    Thanks Ronan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •