Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Storing Negative Numbers Expand / Collapse
Author
Message
Posted Monday, November 10, 2008 11:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Grant Fritchey (11/10/2008)
Not unless I've missed it to. If the value of a number is -3, it's -3, not 3 with some negative sign kept elsewhere that may or may not change it. That's a bit of a freaky design. Although, I'd have to check with an accounting friend for the details, I think there is a method of accounting whereby you designate everything as a value and then describe the value as either a credit or debit, + or -, maybe that's what you're dealing with.


The technique that you are talking about is quite widely used in accounting applications. I remember doing the same thing myself. You take the sum of all debits & multiply it with -1 , take sum of all credits and then just the sum the total of debits and credits to get the current amount. I think this is quite efficient too. But i beleive this can be applied only to some scenarios accounting being one. Otherwise a negative values needs to stored as negative.

You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report.


"Keep Trying"
Post #600410
Posted Tuesday, November 11, 2008 2:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Evidently never heard of twos compliment.

As Lynn Pettis wrote:

"Not all credits are negative. It depends if they are expenses, liabilities, or assests."

Thus it makes no sense not to allow negative values. Thus even splitting into debits and credits you'll need a sign column.

The problems with the design as I see it will be when you want to sum, average, etc. the values. The only way I can see you'll be able to do it with the design constraint you've been given is to setup a cursor to loop through each value and if the sign value is positive leave the value alone, and if the sign value is negative then you'll need to times the amount by -1. Seems like a massive performance hit for no good reason, and one that you'll eventually probably have to revert anyway.

Who was the numbskull who came up with that design? Were they a DBA - or even someone with any programming experience at all?


Random Technical Stuff
Post #600499
Posted Tuesday, November 11, 2008 2:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 3, 2011 7:09 AM
Points: 233, Visits: 494
Chirag (11/10/2008)


You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report.


Wouldn't you just apply an ABS function on the value?


Random Technical Stuff
Post #600500
Posted Tuesday, November 11, 2008 5:03 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #600570
Posted Tuesday, November 11, 2008 6:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
Thanks for the comments (love it Jeff). I agree that accounting would be a different scenario, heck, maybe even storing an OrderAdjustments table where you have an AdjustmentType (SalesTaxState, SalesTaxFederal, Shipping, Packaging, Coupon Discounts, etc.) where all amounts could be stored in positive form because the "Type" tells you what the amount is (+ or -) (even though I would personally store those as +/- values).

However, in the situation I am in, I do agree that the UI should make it easy on the user, but store it in one column as positive or negative amount. However, I've already lost the battle because "We don't know what future requirements will be...", really? So it's fun to just hear all the other lame requirements out there.



Post #600600
Posted Tuesday, November 11, 2008 7:25 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 24, 2014 11:52 AM
Points: 9,294, Visits: 9,484
krayknot (11/10/2008)
tymberwyld (11/10/2008)
Basically what's going on here is that someone is looking at some wireframes of the UI and that's getting translated directly into a database design. If they want to give users an easy way to enter Postive / Negative amounts, by all means, give them a stupid designator / operator selector. However, that should not be how it's stored in the database. As far as accounting goes, I understand your point, but I would think that all Credits would be stored as Negative amounts, yes?


whatever the value and the operator the user selects. you multiply that value by -1.
for example
user entered 100 and selects '-'
the output would be: 100 * -1 = -100
or 100 * 1 =100 or no change


Actually, for a wireframe UI, the "operators" could easily be 2D (or 3D) unit vectors. In which case, it may be perfectly correct to say that the "magnitude" field should never be negative, as that could mess up a lot of rendering software.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #600657
Posted Tuesday, November 11, 2008 7:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:55 AM
Points: 1,330, Visits: 19,306
Jeff Moden (11/10/2008)
Heh... if you really want to throw a ball of wax at them before you decide to find a place with reasonable requirements, tell them the column should be named "IsNotNegative"... :D;)


Lol - that's just mean. Reminds me of the one time they let me lead "Simon Says" in grade school -

Simon says hop on one foot.

Don't stop.



---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #600670
Posted Tuesday, November 11, 2008 10:26 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 20,744, Visits: 32,558
Jeff Moden (11/11/2008)
Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this.


I'll have to look into this. We use PeopleSoft where I work.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #600808
Posted Tuesday, November 11, 2008 11:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
ta.bu.shi.da.yu (11/11/2008)
Chirag (11/10/2008)


You do not store either credit or debit values as negative becoz u need to take these values and show them in various reports .This would need multiplying with -1 each time before showing a report.


Wouldn't you just apply an ABS function on the value?


Agreed.

Lets say we are storing credit amounts as negative values. In many places we need to show these as positive values. So in all such cases we would have to use ABS function. So if we were storing credit amounts as postive value we need not do this.

Iam not defending either design scenarios but like everything
the correct answer is "it depends". Depends on the type of queries that are going to hit the db, the frequency these queries, the amount of data and the overall db dbdesign.


"Keep Trying"
Post #601139
Posted Tuesday, November 11, 2008 11:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:02 PM
Points: 35,397, Visits: 31,955
Lynn Pettis (11/11/2008)
Jeff Moden (11/11/2008)
Oh my... I just remembered where the heck such a requirement for all positive numbers may have come from... PeopleSoft software. Seems like the last folks that I knew that needed to interface with it also went through this.


I'll have to look into this. We use PeopleSoft where I work.


Perfect... I wasn't involved that much in what they were doing and it would be good to know for sure. Thanks, Lynn.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #601151
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse