Query Help

[snaroliya]

Hi All,

 

I am using 5.1.41.

 

I am re-posting this with more simplified manner, i have the following two table with data, you can also download the attach SQL to get the tables structure and data:

 

Table tariff with data

 

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

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');

 

table integer with 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 this query

 

SELECT DATE_FORMAT(DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ), '%a') AS day, 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 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' ORDER BY thedate, ht.type, ht.tariff

 

i am getting the following result set

 

day 	thedate 	room_plan tariff 	type
Tue 	2010-08-31 	EP 	1000 	default
Tue 	2010-08-31 	CP 	1500 	default
Tue 	2010-08-31 	MAP 	2000 	default
Tue 	2010-08-31 	API 	2500 	default
Tue 	2010-08-31 	EP 	1200 	exception
Tue 	2010-08-31 	CP 	1700 	exception
Tue 	2010-08-31 	MAP 	2200 	exception
Tue 	2010-08-31 	API 	2700 	exception
Wed 2010-09-01 	EP 	1000 	default
Wed 2010-09-01 	CP 	1500 	default
Wed 2010-09-01 	MAP 	2000 	default
Wed 2010-09-01 	API 	2500 	default
Thu 	2010-09-02 	EP 	1000 	default
Thu 	2010-09-02 	CP 	1500 	default
Thu 	2010-09-02 	MAP 	2000 	default
Thu 	2010-09-02 	API 	2500 	default

 

now the problem, look at the first 8 rows, these 8 rows returning tariff for 31 August 2010 (2010-08-31), what i want if there is 'exception' type tariff exist, the query should return the 'exception' type tariff not the 'default'. so the first 4 rows should not be return. thats all. hope its clear to understand, if not so, please let me know if you have any doubt.

 

Any help will be highly appreciated.

 

Kind Regards

Sanjeev.

 

 

[attachment deleted by admin]

[jskywalker]

SELECT 
  DATE_FORMAT(DATE_ADD( '2010-08-31', 
  INTERVAL 10 * t.i + u.i DAY ), '%a') AS day, 
  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 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' 
ORDER BY thedate, ht.type, ht.tariff

 

add ht.type='exception' the the WHERE-clause ?

[snaroliya]

Hi jskywalker,

 

Thanks for your reply, you mean to put ht.type='exception' in WHERE clause? if we put this only exception type tariff will be return and the default tariff never be return, what i want if there are exception type tariff exist it should return first else the default one.

 

 

[jskywalker]

will tariff be always higher for an exception tariff?

 

if yes:

SELECT   
DATE_FORMAT(DATE_ADD( '2010-08-31',   
INTERVAL 10 * t.i + u.i DAY ), '%a') AS day,   
DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate,   
ht.room_plan,   
max(ht.tariff),   
max(ht.type) 
FROM 
integers AS t 
CROSS JOIN integers AS u 
LEFT OUTER JOIN 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' 
GROUP BY thedate, room_plan
ORDER BY thedate, ht.type, ht.tariff;

 

2x max(), and GROUP BY added...

 

above only works because 'exception' comes after 'default' , and it will give the highest tariff when comparing default en expection tarrifs....

[snaroliya]

yes this query works, but the exception tariff may be lower or higher, its not always higher 

 

 

[jskywalker]

lol, i expected you to say that 

 

SELECT   
DATE_FORMAT(DATE_ADD( '2010-08-31',   
INTERVAL 10 * t.i + u.i DAY ), '%a') AS day,   
DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate,   
ht.room_plan as room_Plan,   
ht.tariff default_tariff,   
hte.tariff exception_tariff,
if (ISNULL(hte.room_plan), ht.tariff, hte.tariff) TARIFF,
if (ISNULL(hte.room_plan), ht.type, hte.type) TYPE
FROM 
integers AS t 
CROSS JOIN integers AS u 
LEFT OUTER JOIN tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to 
LEFT OUTER JOIN tariff AS hte ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN hte.date_from AND hte.date_to AND ht.room_plan=hte.room_plan AND ht.room_type_id=hte.room_type_id AND hte.type = 'exception'
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 ht.type = 'default'
GROUP BY thedate, room_plan
ORDER BY thedate, ht.type, ht.tariff;

 

+------+------------+-----------+----------------+------------------+--------+-----------+
| day  | thedate    | room_Plan | default_tariff | exception_tariff | TARIFF | TYPE      |
+------+------------+-----------+----------------+------------------+--------+-----------+
| Tue  | 2010-08-31 | EP        |           1000 |             1200 |   1200 | exception |
| Tue  | 2010-08-31 | CP        |           1500 |             1700 |   1700 | exception |
| Tue  | 2010-08-31 | MAP       |           2000 |             2200 |   2200 | exception |
| Tue  | 2010-08-31 | API       |           2500 |             2700 |   2700 | exception |
| Wed  | 2010-09-01 | EP        |           1000 |             NULL |   1000 | default   |
| Wed  | 2010-09-01 | CP        |           1500 |             NULL |   1500 | default   |
| Wed  | 2010-09-01 | MAP       |           2000 |             NULL |   2000 | default   |
| Wed  | 2010-09-01 | API       |           2500 |             NULL |   2500 | default   |
| Thu  | 2010-09-02 | EP        |           1000 |             NULL |   1000 | default   |
| Thu  | 2010-09-02 | CP        |           1500 |             NULL |   1500 | default   |
| Thu  | 2010-09-02 | MAP       |           2000 |             NULL |   2000 | default   |
| Thu  | 2010-09-02 | API       |           2500 |             NULL |   2500 | default   |
+------+------------+-----------+----------------+------------------+--------+-----------+
12 rows in set (0.00 sec)

[snaroliya]

hey jskywalker,

 

You rock!  the query works like a charm, i posted this in many forum but no one willing to reply even but you rock like a star, thanks a lot!

 

Kind Regards

Sanjeev

[snaroliya]

hey jskywalker,

 

i need your help again, the query is not working when i try to select all the rooms, i inserted one more room tariff for room_type_id = 1 and remove the room_type_id = 2 from where clause to get all the rooms tariff and run the following query but its only returning room_type_id = 2 tariff.

 

you can find the new tariff table with data in the attach file and the query below, whats going wrong... please help...

 

SELECT DATE_FORMAT(DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ), '%a') AS day, DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY) AS thedate, ht.room_plan as room_plan, ht.tariff default_tariff, hte.tariff exception_tariff, if (ISNULL(hte.room_plan), ht.tariff, hte.tariff) tariff, if (ISNULL(hte.room_plan), ht.type, hte.type) type FROM integers AS t CROSS JOIN integers AS u LEFT OUTER JOIN tariff AS ht ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN ht.date_from AND ht.date_to LEFT OUTER JOIN tariff AS hte ON DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) BETWEEN hte.date_from AND hte.date_to AND ht.room_plan=hte.room_plan AND ht.room_type_id=hte.room_type_id AND hte.type = 'exception' WHERE DATE_ADD( '2010-08-31', INTERVAL 10 * t.i + u.i DAY ) <= DATE_SUB('2010-09-03', INTERVAL 1 DAY) AND ht.type = 'default' GROUP BY thedate, room_plan ORDER BY thedate, ht.type, ht.tariff

 

Kind Regards

Sanjeev.

 

 

[attachment deleted by admin]

[jskywalker]

sorry for the late response,

 

but i'm not going to solve your problem

 

maybe you should hire someone who can do this for you?