MySQL – Using CASE Function Between Dates

caseMySQL

SQL Version: MySQL 5.6.32-78.1

I have this table below:

+-----+----------+--------------+--------------+--------------+------------+--------------+
| id  | amount   | currency     | date_created | from_date    | thru_date  | product_code |
+-----+----------+--------------+--------------+--------------+------------+--------------+
| 101 | 21       |  DUSD        | 2018-01-01   | 2018-01-01   | 2018-01-20 | 01           |
| 101 | 22       |  USD         | 2018-01-02   | 2018-01-21   | NULL       | 01           |
+-----+----------+--------------+--------------+--------------+------------+--------------+

I have a date when a sale was made on 2018-01-25 and I try using a query with this line

CASE WHEN so.date_created >= pp.date_created 
      AND so.date_created BETWEEN pp.from_date AND pp.thru_date 
         THEN pp.amount 
         ELSE pp.amount 
END

Somehow it does not want to work.
It should display 22 but it still displays 21

Could someone help me?

Thanks!

Best Answer

Look on fiddle

SELECT pp.*, so.date_created sale_date_created
FROM pp
JOIN so  ON so.date_created >= pp.date_created
        AND so.date_created BETWEEN COALESCE(pp.from_date, so.date_created) AND COALESCE(pp.thru_date, so.date_created);

COALESCE() is used to prevent discarding records containing NULL(s).

Related Question