CODEWITHSUNDEEP
mysqlsqldatedatetimebetween
XTRUST.ORG
XTRUST.ORG

Reputation: 3402

Conditional select between dates

I have a table and would like to find minimum and maximum values of price. I would like to get minimal price from action_table when current date between "from" and "to".

from        to          action_price  price
2015-04-02  2015-08-02  20            25
2015-04-02  2015-04-20  0             30
2015-04-03  2015-04-21  0             40

So from the table above I need: min->20 (because current date between "from"/"to") and max->40

I have tried something like that, but don't work as expected:

SELECT 
CASE WHEN curdate() BETWEEN from AND to THEN MAX(action_price) ELSE MAX(price) END AS max,
CASE WHEN curdate() BETWEEN from AND to THEN MIN(action_price) ELSE MIN(price) END AS min
FROM `table`;

Upvotes: 9

Views: 1325

Answers (3)

Felix Geenen
Felix Geenen

Reputation: 2707

I think a "HAVING" statement will work here:

http://sqlfiddle.com/#!9/4653c/4

Upvotes: 2

Nighthunter22
Nighthunter22

Reputation: 273

This should work for getting the minimum value in action_price.

SELECT MIN(action_price) 
FROM yourdb 
WHERE DATE_FORMAT(CURDATE(), '%Y-%m-%d') > from 
  AND DATE_FORMAT(CURDATE(), '%Y-%m-%d') < to;

I tried this on fiddle, and worked, I just renamed 'from' and 'to' column since they are reserved words on MySQL.

As for the MAX value, I don't know what you exactly want, what would be the condition to get that MAX value? Same as for MIN? Anyway, let me know and I'll improve my answer with the correspond query for it.

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272126

If I understand correctly, you want minumum and maximum of these values:

25 20, 30, 40

You simply need to wrap the case statement inside the aggregate functions instead of other way round:

SELECT
MIN(CASE WHEN CURDATE() BETWEEN `from` AND `to` THEN action_price ELSE price END) AS `min`,
MAX(CASE WHEN CURDATE() BETWEEN `from` AND `to` THEN action_price ELSE price END) AS `max`
FROM action_table;

+------+------+
| min  | max  |
+------+------+
|   20 |   40 |
+------+------+

Upvotes: 7

Related Questions