0

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!

  • 1
    THEN pp.amount ELSE pp.amount means 'pp.amount in any case'... :) – Akina Sep 13 '18 at 5:57
  • It should display 22 but it still displays 21 Something is wrong. The output must be empty because none of records you show match both conditions. First record do not match '2018-01-25' BETWEEN '2018-01-01' AND '2018-01-20', second do not match because of NULL value. – Akina Sep 13 '18 at 6:04
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.