SQL Query - Positive/Negative Values

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 2000 Forums

Transact-SQL (2000)

SQL Query - Positive/Negative Values

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-29 : 10:19:57

Simon writes "Hi,

I'm building a query to pull financial data from my finance application via ODBC. Problem is that in the financial database some figures are represented as negatives and I want to show them as positives (they're stored as negatives due to the debit/credit way in which accounting works). Is there anyway I can convert a negative result to a positive one and vice versa?

ie select * from finance_table returns a series of numbers, all negative - I want to show them as positive.

Thanks"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 10:25:20

select mycol, -mycol
from yourtable

select mycol, -1 * mycol
from yourtable

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-01-29 : 10:31:13

ABS() ?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-01-29 : 13:03:27

Yeah, just use ABS(mycol). But beware of summing them... you might want to use ABS for displaying the answer, but if you need to use some sort of running total, you will probably want to be handling it slightly differently.

For financial transactions, you may want to display the result in two columns, debit and credit. But just be careful what you do - you don't want something that is supposed to be a negative value coming up as positive and stopping your figures from balancing.

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-29 : 13:34:35

quote:
Originally posted by AskSQLTeam

Is there anyway I can convert a negative result to a positive one and vice versa?

Do the "vice versa" comply with ABS use?

Peter Larsson
Helsingborg, Sweden

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-01-29 : 13:51:54

Yeah, if the vice-versa applies, then multiplying by -1 or subtracting from 0 are the ways to go.

But his 'ie' line suggests that he really just wants to convert from negative to positive - which isn't necessarily the right thing to do in financial transaction processing.

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-29 : 15:47:56

You should allow for the possibility that an account can have an entry that doesn't have the same sign as the normal sign for that account. For example, a sales return might be represented with the opposite sign as a sale.

You might need a table that shows you the "normal" sign for each account.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources