CODEWITHSUNDEEP
sqlsql-server
user19084244
user19084244

Reputation:

Round negative result from CASE WHEN statement to zero

Let's say I have this query

SELECT [A], [B], [C], [D], [E] CASE  WHEN [A] <> '' THEN [B] + [C]  WHEN [A] = '' THEN [B] - [C]  ELSE ''  END AS [Result]  FROM [TableA]

But, [B] - [C] resulting a negative value and I wanted to round it to zero, any idea how?

Upvotes: 0

Views: 83

Answers (2)

Stu
Stu

Reputation: 32619

You could simplify the case expression by using a values contructor to first evaluate B - C and then use this in the case expression; there are only two possibilities when comparing A being equal or not-equal (not considering nullability).

select A, B, C, D, E,
  case when A = '' then BminusC else B + C end as Result  
from TableA
cross apply(values(Iif(B - C < 0, 0, B - C)))v(BminusC);

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

You could use another nested CASE expression:

SELECT [A], [B], [C], [D], [E],
       CASE WHEN [A] <> '' THEN [B] + [C]
            WHEN [A] = '' THEN
                CASE WHEN [B] - [C] < 0 THEN 0 ELSE [B] - [C] END
            END AS [Result]
FROM [TableA];

I removed the ELSE condition in the outer CASE expression, because it appears that this CASE expression is generating numeric values. Therefore, it doesn't make sense to have empty string as one possible value.

Upvotes: 0

Related Questions