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(8) 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),
(8),
(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.