| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-28-11, 02:41
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 33
|
|
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.
|
|
01-28-11, 03:58
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 623
|
|
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.
|
|
01-28-11, 04:25
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 33
|
|
|
|
Quote:
Originally Posted by it-iss.com
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
|
|
01-28-11, 05:06
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 623
|
|
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';
|
|
01-28-11, 05:17
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 33
|
|
Quote:
Originally Posted by it-iss.com
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
|
|
01-28-11, 05:41
|
|
Registered User
|
|
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 623
|
|
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).
|
|
01-31-11, 00:29
|
|
Registered User
|
|
Join Date: Mar 2008
Posts: 33
|
|
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
MySQL: Special Price Scenario
