Sometime in the past, I came across this piece of SQL code:
(CASE WHEN (a.DebitBalance – b.TotScaledDown) > 0 THEN a.DebitBalance – b.TotScaledDown ELSE 0 END)
Logically not wrong, but the number of mathematical operations can be cut further to increase processing speed.
Zoom in here
(a.DebitBalance – b.TotScaledDown) > 0
There is an arithmetic operation and a comparison operation here. This can be reduced to a single comparison operation with no loss in logic. The arithmetic operation is only executed when the expression is true, otherwise the literal 0 is returned.
(CASE WHEN (a.DebitBalance > b.TotScaledDown) THEN a.DebitBalance – b.TotScaledDown ELSE 0 END)
Of course I may be wrong as I was not able to do any benchmarking to compare the code performance.