Search code examples
sqlsql-serverwhere-clausecase

Make CASE for only specified Condition


I have a table that has 2 columns : Balance, RateCol

I want to make a query, with condition like this

X is 2.25
when Balance > 0 then Total = Balance, and RateCol > X
When Balance > 2000000000 then Total = Balance - 2000000000, and RateCol <= X

The first thing I tried is using CASE, but the result is not like what I wanted

Query :

SELECT 
    Balance, 
    CASE 
        WHEN Balance > 0 AND RateCol > 2.25 THEN Balance
        WHEN Balance > 2000000000 AND RateCol <= 2.25 THEN Balance - 2000000000 
    END AS Total,
    RateCol
FROM
    MyTable

Result:

Balance Total RateCol
4428600457.50 2428600457.50 1.400000
15339365000.00 15339365000.00 3.250000
335500000.00 NULL 0.750000 < Anomaly
347188820.00 NULL 0.750000 < Anomaly

As you can see from my result, the anomalies somehow also get included even thought it's outside of my defined scope.

What I thought is this happening because I didn't defined ELSE for condition I didn't specify, but I only need result from the condition I did specify, is there a way to achieve this?

I've also tried to add WHERE on my query like this :

SELECT 
    Balance, 
    CASE 
        WHEN Balance > 0 THEN Balance
        WHEN Balance > 2000000000 THEN Balance - 2000000000 
    END AS Total,
    RateCol
FROM  
    MyTable
WHERE
    RateCol = CASE 
                  WHEN Balance > 0 THEN RateCol > 2.25
                  WHEN Balance > 2000000000 THEN RateCol <= 2.25

But CASE cannot take comparison expression, and I don't know what to do anymore

Expected result :

Balance Total RateCol
4428600457.50 2428600457.50 1.400000
15339365000.00 15339365000.00 3.250000

(Anomalies not included)


Solution

  • WHERE
        RateCol = CASE 
                      WHEN Balance > 0 THEN RateCol > 2.25
                      WHEN Balance > 2000000000 THEN RateCol <= 2.25
    

    Isn't this attempt equivalent to:

    WHERE (Balance > 0          AND RateCol >  2.25)
       OR (Balance > 2000000000 AND RateCol <= 2.25)