ABS function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

ABS function

Author

Topic

igator210
Starting Member

5 Posts

Posted - 2012-05-14 : 14:36:30

Sorry for these posts, but I really can't find examples on the net of what I need, so I'm turning to you guys.

Would the following formula work in TSQL?

(ABS([A]-[B])/(([A]+[B])/2))*100

Or does it need tweaking?

Nate

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-14 : 15:42:31

It would.

If A and B are columns of integer data types, you may want to force floating point division - you can do that by forcing the numerator or denominator to be decimal/numeric, for example like this:

(ABS([A]-[B])/(([A]+[B])/2.0))*100

You could also possibly simplify the logic like this - but no need to if what you have is more readable to you.

100*ABS([A]-[B])*2.0/([A]+[B])

Another thing you might want to consider is avoiding division by zero. You can force the denominator to null if the denominator is zero to do that like this:

100*ABS([A]-[B])*2.0/NULLIF(([A]+[B]),0)

igator210
Starting Member

5 Posts

Posted - 2012-05-14 : 15:58:45

Thanks. I'm in the process of learning when I can type a formula directly or when I need other syntax such as Case / When / Then.

EDIT: the formula is written that way purely out of industry standard. If anyone else besides me wanted to look at the equation, they would know what is was used for.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-14 : 16:27:55

200 * ABS(A - B) / (A + B)

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts

Posted - 2012-05-14 : 16:38:37

quote:
Originally posted by sunitabeck

..Another thing you might want to consider is avoiding division by zero. You can force the denominator to null if the denominator is zero to do that like this:
100*ABS([A]-[B])*2.0/NULLIF(([A]+[B]),0)

So wouldn't the final result-set be NULL if the denominator also is NULL ?

After Monday and Tuesday even the calendar says W T F ....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-05-14 : 18:26:04

The only way for the division to be zero, is of A or B (or both) is allowed to be negative.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources