Query Help

snaroliya · June 5, 2010

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 · June 5, 2010

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 · June 5, 2010

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 · June 5, 2010

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 · June 5, 2010

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

jskywalker · June 5, 2010

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 · June 5, 2010

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 · June 8, 2010

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 · June 12, 2010

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?

Sign In

Query Help

Recommended Posts

snaroliya

Link to comment

Share on other sites

jskywalker

Link to comment

Share on other sites

snaroliya

Link to comment

Share on other sites

jskywalker

Link to comment

Share on other sites

snaroliya

Link to comment

Share on other sites

jskywalker

Link to comment

Share on other sites

snaroliya

Link to comment

Share on other sites

snaroliya

Link to comment

Share on other sites

jskywalker

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information