Query Optimization

[snaroliya]

Hi All,

 

I am using MySql ver. 5.1.41.

 

I have the following table structure for storing apartment tariff within a date range.

 

CREATE TABLE IF NOT EXISTS `tariff` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `room_type_id` int(10) unsigned NOT NULL,
  `room_plan` char(3) NOT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `tariff` mediumint( unsigned NOT NULL,
  `type` char(10) NOT NULL DEFAULT 'default',
  PRIMARY KEY (`id`)
)ENGINE=MyISAM  DEFAULT CHARSET=utf8

where 'room_type_id' stand for different type of room and 'room_plan' stand for different room plan abbreviation like for room only its "EP", with breakfast "CP" so on... and 'type' stand for tariff which is 'default' or 'exception'.

 

here default i mean tariff for a range of dates say 01 JAN 2010 to 31 DEC 2010 and exception like where tariff changes like 15 MAY 2010 to 15 JUN 2010 or 16 JUN 2010 to 31 AUG 2010 in the example data for room_type_id = 2, you get a better understanding with the data i am supplying below...

 

INSERT INTO `tariff` (`id`, `room_type_id`, `room_plan`, `date_from`, `date_to`, `tariff`, `type`) VALUES
(1, 2, 'EP', '2010-01-01', '2010-12-31', 1000, 'default'),
(2, 2, 'CP', '2010-01-01', '2010-12-31', 1500, 'default'),
(3, 2, 'MAP', '2010-01-01', '2010-12-31', 2000, 'default'),
(4, 2, 'API', '2010-01-01', '2010-12-31', 2500, 'default'),
(64, 2, 'API', '2010-06-16', '2010-08-31', 2700, 'exception'),
(63, 2, 'MAP', '2010-06-16', '2010-08-31', 2200, 'exception'),
(62, 2, 'CP', '2010-06-16', '2010-08-31', 1700, 'exception'),
(61, 2, 'EP', '2010-06-16', '2010-08-31', 1200, 'exception'),
(58, 2, 'CP', '2010-05-01', '2010-06-15', 1600, 'exception'),
(59, 2, 'MAP', '2010-05-01', '2010-06-15', 2100, 'exception'),
(57, 2, 'EP', '2010-05-01', '2010-06-15', 1100, 'exception'),
(60, 2, 'API', '2010-05-01', '2010-06-15', 2600, 'exception'),
(17, 1, 'EP', '2010-01-01', '2013-06-30', 3453, 'default'),
(18, 1, 'CP', '2010-01-01', '2013-06-30', 6876, 'default'),
(19, 1, 'MAP', '2010-01-01', '2013-06-30', 4563, 'default'),
(20, 1, 'API', '2010-01-01', '2013-06-30', 7686, 'default'),
(49, 12, 'EP', '2010-03-01', '2013-11-10', 3453, 'exception'),
(50, 12, 'CP', '2010-03-01', '2013-11-10', 6575, 'exception'),
(51, 12, 'MAP', '2010-03-01', '2013-11-10', 3453, 'exception'),
(52, 12, 'API', '2010-03-01', '2013-11-10', 6786, 'exception');

for retrieving the tariff from the above table i am using the integer table trick, here is the 'integer' table structure and data:

 

CREATE TABLE IF NOT EXISTS `integers` (
  `i` int(10) unsigned NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `integers` (`i`) VALUES
(0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(,
(9);

and with the below query i am able to get the tariff for a range of dates, like the below query return tariff between 14 JUN to 16 JUN 2010 both date including:

 

SELECT DATE_FORMAT(DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ), '%a') AS thedayname, DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan, ht.tariff, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN tariff AS ht ON DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-06-14', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-06-17', INTERVAL 1 DAY) AND ht.room_type_id = '2' ORDER BY thedate, ht.type, ht.tariff

you can run the above SQL code to test this, now my problem is that the above query returning both 'default' and 'exception' tariff, i want when there is exception tariff exist the query return only exception tariff not the default.

 

any help will be highly appreciated.

 

Kind Regards

Sanjeev.

[dabaR]

Hi there!

 

The only way that I can think of for that, if it could be done is using EXISTS.

 

So something like this:

  SELECT * FROM table WHERE date = CURDATE() AND type = CASE WHEN EXISTS(SELECT * FROM table WHERE date = CURDATE() AND type = 'exception') THEN 'exception' ELSE 'default';

 

Maybe you should do the testing in the code, so something like:

 

$exception_pricing = query('...');
if (!empty($exception_pricing)) { $normal_pricing = query('...'); }
$pricing = empty($exception_pricing) ? $normal_pricing : $exception_pricing;

[snaroliya]

Hi dabaR,

 

Thanks for your reply, i already try with the CASE statement but no luck, i will try it again today.

 

Kind Regards

Sanjeev

[snaroliya]

HI DabaR,

 

I try it with the 'CASE WHEN EXISTS' but its not working, its always gives the default tariff.

 

did you try this with the structure and data i provided?

 

Thanks a lot for your help

 

Kind Regards

Sanjeev.

 

[dabaR]

What's the code you ran last, and what was the error?

[snaroliya]

i run the following query as per you suggest with the 'CASE WHEN EXIST', its not giving any error but only returning the exception tariff only. here it is:

 

SELECT DATE_FORMAT(DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ), '%a') AS thedayname, DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan, ht.tariff, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = CASE WHEN EXISTS(SELECT * FROM jos_hm_tariff WHERE date_to >= '2010-08-31' AND  date_from <= '2010-09-03' AND room_type_id = '2' AND type = 'exception') THEN 'exception' ELSE 'default' END ORDER BY thedate, ht.type, ht.tariff

 

and this one too

 

SELECT DATE_FORMAT(DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ), '%a') AS thedayname, DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan, ht.tariff, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = CASE WHEN EXISTS(SELECT DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = 'exception' ORDER BY thedate, ht.type) THEN 'exception' ELSE 'default' END ORDER BY thedate, ht.type, ht.tariff

 

[dabaR]

Hmmm...

You might want to try:

...AND type = (SELECT CASE WHEN EXISTS(SELECT * FROM jos_hm_tariff WHERE date_to >= '2010-08-31' AND  date_from <= '2010-09-03' AND room_type_id = '2' AND type = 'exception') THEN 'exception' ELSE 'default' END)...

[snaroliya]

i try this but same result

 

SELECT DATE_FORMAT(DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ), '%a') AS thedayname, DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan, ht.tariff, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = (SELECT CASE WHEN EXISTS(SELECT DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = 'exception' ORDER BY thedate, ht.type) THEN 'exception' ELSE 'default' END ) ORDER BY thedate, ht.type, ht.tariff

 

also i try the CASE query independently, and its returning only 'exception' single results, i think this is the problem as i need type for each record, here it is:

 

SELECT CASE WHEN EXISTS(SELECT DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN jos_hm_tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.room_type_id = '2' AND type = 'exception' ORDER BY thedate, ht.type) THEN 'exception' ELSE 'default' END

[dabaR]

Well you did originally say you want to see only exception when there is exception. Are you saying this gives back exception when there really isn't an exception in the DB?

[snaroliya]

let me explain it again, the original query i posted first, giving me both default and exception tariff, what i want when there is a exception tariff range, it should be return the exception tariff for the dates not the default tariff.

 

if you ran the query you can see what i mean.

 

 

[dabaR]

OK, I have no more ideas. You can perhaps try reposting your question.

[fenway]

Double -post.