Conditional select between dates

Question

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`;

Answer 1

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 |
+------+------+