10 replies to this topic

[cancer10]

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

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:

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:

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.

[LuthfiHakim]

I'll start with the most obvious question, "why is this happening?". The answer is simple, because only record with id 2 that matches both criterias. Let's see record with id 1, it does not match "date_start >= '2011-01-12'", since it's date_start is "2011-01-01". Record with id 3 does not match with second criteria, "date_end <= '2011-01-18'", since its date_end is "2011-01-01" which is way larger than "2011-01-18".

Quote

Ideally the query should fetch all the 3 rows since they meet my query.

Then you need to rewrite your query to really matches the requirement. Hint: you need a couple of ORs... :c-cool: More hint: start by describing/listing possible combinations of date_start and date_end that should match your "range".:c-whistle:

[cancer10]

LuthfiHakim said:

I'll start with the most obvious question, "why is this happening?". The answer is simple, because only record with id 2 that matches both criterias. Let's see record with id 1, it does not match "date_start >= '2011-01-12'", since it's date_start is "2011-01-01". Record with id 3 does not match with second criteria, "date_end <= '2011-01-18'", since its date_end is "2011-01-01" which is way larger than "2011-01-18".



Then you need to rewrite your query to really matches the requirement. Hint: you need a couple of ORs... :c-cool:

Hi LuthfiHakim,

Thanks for the reply. Now I get all the three records by running where Date_start <= '2011-01-18' AND Date_end >= '2011-01-12'. 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:

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:

    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

[LuthfiHakim]

cancer10 said:

Now I get all the three records by running where Date_start <= '2011-01-18' AND Date_end >= '2011-01-12'. However, it does not meet what I am actually looking for.

:) Then please pay attention to the "hints" :c-whistle:

cancer10 said:

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:

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:

    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?

Sure it can be done. However you need another table/view to provide the start and end dates for each special price "type", so we know that for special price id 1 it's from 12th to 15th, and for special price id 2 it was only for 16th, and so on. And you have to define the relationship between the new table with the special prices table. It could be directly, or indirectly (e.g. using another table/view).

[EDIT]
Sorry, I just realized that you wanted to use the date_start and date_end that actually in special_prices table to calculate the days? Well, just use the DATE_DIFF on them and you'll get what you after. :)

SELECT

  `id`,

  `date_start`,

  `date_end`,

  `special_price`,

  `status_id`,

  (DATEDIFF('date_end','date_start')+1)*special_price AS DD

FROM `special_prices`

WHERE date_start <= '2011-01-18' AND date_end >= '2011-01-12' 

[cancer10]

LuthfiHakim said:

Sure it can be done. However you need another table/view to provide the start and end dates for each special price "type", so we know that for special price id 1 it's from 12th to 15th, and for special price id 2 it was only for 16th, and so on. And you have to define the relationship between the new table with the special prices table. It could be directly, or indirectly (e.g. using another table/view).

Well the hotel booking start and end date is to be provided by the end customer. How can this be stored in the db beforehand? Or perhaps I am not following you?

[LuthfiHakim]

^No, it was me that was not following you. Please read my previous (edited) post. However I think this is flaw in your db design, but I could be wrong since I am not familiar with the actual business requirements and haven't seen larger view of the design.

[cancer10]

Hi

That query of yours is gettng me this result

    id  date_start  date_end    special_price  status_id     DD  

------  ----------  ----------  -------------  ---------  -------

     1  2011-01-01  2011-01-15         100.00          1  1500.00

     2  2011-01-16  2011-01-16         500.00          1   500.00

     3  2011-01-17  2011-01-31         100.00          1  1500.00

Where as the actual result should be:

    id  date_start  date_end    special_price  status_id     DD  

------  ----------  ----------  -------------  ---------  -------

     1  2011-01-01  2011-01-15         100.00          1  400.00

     2  2011-01-16  2011-01-16         500.00          1   500.00

     3  2011-01-17  2011-01-31         100.00          1  200.00

Thanks for your time :)

[LuthfiHakim]

Well yes, that's because I thought you wanted to use the date_start and date_end values from the table as is, not to compare it first with the criteria range. In this case you must rely on CASE WHEN to compare the fields agains the criterias.

E.g.

  SELECT

    blah

    , blah

    ...

    CASE

       WHEN Date_Start > `somedate` THEN DATEDIF()*xxx

       WHEN Date_end > `somedate` THEN DATEDIF()*xxx

       ...

    END AS DD


  FROM

    ...

Please check MySQL manual reference to see more about CASE WHEN :c-thumbup:

[Parabola]

This is just spitballing, but what if the query was more like:

SELECT     id, date_start, date_end, special_price

FROM         special_prices

WHERE     (date_start >= @StartDate) AND (date_end >= @StartDate) OR

                      (date_start <= @EndDate) AND (date_end <= @EndDate)

Which for me returned:
1 1/1/2011 1/15/2011 100.00
2 1/16/2011 1/16/2011 500.00
3 1/17/2011 1/31/2011 100.00
NULL NULL NULL NULL


I used the variables of course so that they could be used in a form.... do whatever you want, but that seems to work for me :)

[Parabola]

My bad....
I take it back. I added another row, which had dates way off... and it was returned....
SO
use between:

SELECT     id, date_start, date_end, special_price

FROM         special_prices

WHERE     (date_start BETWEEN @StartDate AND @EndDate) OR

                      (date_end BETWEEN @StartDate AND @EndDate)

Which worked beautifully... also a bit simpler that datediff and whatnot :c-biggrin:

[cancer10]

Thanks Parabola and everyone who posted.