Having this table

trans_date | settle_date | type
2022-06-08 | 2022-06-09  |  In
2022-06-08 | 2022-06-09  |  In
2022-06-08 |             |  Out
2022-06-09 |             |  Out

I want to only select:

1. If type is In, use settle_date
2. If type is Out, use trans_date

I am doing this:

SELECT * 
  FROM tx 
    WHERE DATE(CASE WHEN tx.type= 'IN' 
                    THEN tx.settle_date 
                    ELSE tx.trans_date END) <= '2022-06-08' .
       OR DATE(CASE WHEN tx.type = 'IN' 
                    THEN tx.settle_date 
                    ELSE tx.trans_date END) <= '2022-06-09'

but everything is select out, I expected the last row to be excluded.

I want IN to use <= 2022-06-09 and OUT to use <= 2022-06-08

CodePudding user response：

You're only choosing the column in the CASE expression, not the dates to compare with. Then you're selecting a row if that column is less than either of the dates, because of the OR condition.

Put the whole comparison in the CASE expression, not just the column name.

SELECT *
FROM tx
WHERE CASE tx.type
    WHEN 'IN' THEN tx.settle_date <= '2022-06-09'
    ELSE tx.trans_date <= '2022-06-08'
END

DEMO

CodePudding user response：

You said "..I expected the last row to be excluded.", which I assume is your expected result.. so could you perhaps mean something like this:

SELECT *,
       CASE WHEN tx.type= 'In' 
             AND tx.settle_date <= '2022-06-09' 
                    THEN tx.settle_date 
            WHEN tx.type= 'Out' 
             AND tx.trans_date <= '2022-06-08' 
                    THEN tx.trans_date END AS f_date
FROM tx
HAVING f_date IS NOT NULL

Instead of doing WHERE, you probably want to do it in SELECT then filter the result afterward.

Demo fiddle