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