Round negative result from CASE WHEN statement to zero-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.