SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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



    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
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Date_start of the first row is smaller than '2011-01-12' and thus doesn't meet the WHERE condition.
    Date_end of the 3rd row is bigger than ''2011-01-18' and thus doesn't meet the WHERE condition.

    What you want is all rows where Date_start <= '2011-01-18' AND Date_end >= '2011-01-12' , that way you also get the rows that are only partially in the date range you specified in the WHERE clause.

  3. #3
    SitePoint Guru phantom007's Avatar
    Join Date
    May 2008
    Posts
    737
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Date_start of the first row is smaller than '2011-01-12' and thus doesn't meet the WHERE condition.
    Date_end of the 3rd row is bigger than ''2011-01-18' and thus doesn't meet the WHERE condition.

    What you want is all rows where Date_start <= '2011-01-18' AND Date_end >= '2011-01-12' , that way you also get the rows that are only partially in the date range you specified in the WHERE clause.
    Hi Guido,

    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


Bookmarks

Posting Permissions

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