Best practice for number comparisons in SELECT statement

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

SELECT Variance = CASE WHEN SYS >= 0.0 THEN SYS - BANK ELSE SYS + BANK END

FROM myTable;

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Or if you dislike CASE statements (some people do):

SELECT Variance = SYS + SIGN(SYS) * (-1) * BANK

FROM myTable;

The only problem is when SYS is 0. What does your formula equate to? - BANK or just BANK?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

mattech06 SSCrazy Points: 2484 More actions · Answer 3

Hi thanks,

I don't think it will ever be zero but if it is it needs to be treated as if it was a positive

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

mattech06 (6/19/2014)
Hi thanks,
I don't think it will ever be zero but if it is it needs to be treated as if it was a positive

In that case (pun intended), stick with the CASE expression.

Or another alternative:

SELECT Variance = SYS - BANK

FROM MyTable

WHERE SYS >= 0.0

UNION ALL

SELECT Variance = SYS + BANK

FROM MyTable

WHERE SYS < 0.0

There are people who prefer the union all approach as it avoids the case statement and is supposedly faster.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP