Go4Expert

Go4Expert (http://www.go4expert.com/)
-   MySQL (http://www.go4expert.com/forums/mysql-forum/)
-   -   MySQL: Special Price Scenario (http://www.go4expert.com/forums/mysql-special-price-scenario-t24772/)

cancer10 28Jan2011 13:12

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.


All times are GMT +5.5. The time now is 19:49.