Using Case Statement in Where Clause (with column)

Question

I want to use a case statement in a where clause. But the where clause change depends on my equation.

For example:

Table

ID Column1  Column2
1    2         3 
2    4         1
3    5         4
4    4         7

Query

select * from table
where (case when column1>=column2 then column2>3 else column1>3 end)

Expected output

ID Column1  Column2
3    5         4
4    4         7

Solution 1

It sounds like you want your CASE expression to be:

SELECT *
FROM dbo.YourTable
WHERE   CASE 
            WHEN column1 >= column2 AND column1 > 4 THEN 1
            WHEN column1 < column2 AND column2 > 4 THEN 1 
            ELSE 0 
        END = 1

Solution 2

You don't need CASE, you can use OR:

select * from table 
where (column1 >= column2 and column1 > 3)
   or (column1 <  column2 and column2 > 3)