[r937]

hoping PatP reads this thread, as i'm sure i got this information from him

for accounting apps, how many decimal positions do you store? DECIMAL(11,2) or DECIMAL (13,4)?

i've mentioned (on another forum) what i ~thought~ i heard PatP say long ago, that GAAP recommends 4 decimal positions

i've been asked for a citation, and of course googling turns up squadoosh...

[georgev]

Personally, I think it is 4 - but this is based on the fact that mssql money datatype stores 4dp accuracy.

[mike_bike_kite]

Sybase has a money type and I pulled this from the manual -

The money and smallmoney types store four digits to the right of the decimal point, but round up to the nearest hundredth (.01) for display purposes. When data is converted to a money type, it is rounded up to four decimal places.

Mike

[blindman]

Yeah, Rudy. Why not just use the MONEY datatype?

[r937]

as you get older, you lose 3 things
1. your hearing
2. your memory
3. ...
i forget what the third one is
and i forget where i've seen advice never to use MONEY datatype

[blindman]

MONEY is the root of all evil datatypes.

[Pat Phelan]

Due to a change in employment, I no longer have easy access to the GAAP Standards Manual. I'll see what I can do to provide an explicit citation, but that will definitely be a challenge right now.

GAAP does explicitly call for four decimal places below the decimal point (as in 0.0001) for storage (but not necessarily display) of detail currency values. The purpose for the specification is complex, but the primary reason is to permit auditing of the computation processes involved in the production of Audited financial statements (if there are less than four decimal positions used anywhere in the computation of financials, a caveat is required in the Audit Opinion document).

The number of decimal positions that GAAP requires to the left of the decimal place is a bit harder to concretely identify. A rule of thumb that will cover all of the cases in the specifiication is that currency values need to be able to represent values one hundred times the largest value (including totals) that are directly based on the value. This means that if your general ledger totals to a million dollars, then the rule of thumb requires that all entries need to be able to represent one hundred million dollars). In most cases this is overkill, and the underlying rules are FAR more complex (around five pages of fine print), but this rule of thumb covers all cases for the rules.

I'll try to get a citation for you, but the GAAP Standards Manual is very hard to access. I believe that it can only be sold to Licensed CPAs and that they are contracturally committed to rules of conduct for handling the documents. That makes the GAAP Standards Manuals only slightly easier to access than CIA Technical Manuals. Specifically I don't know anywhere that they appear online: public, private, or otherwise.

-PatP

[mike_bike_kite]

Originally Posted by Pat Phelan

The number of decimal positions that GAAP requires to the left of the decimal place is a bit harder to concretely identify. A rule of thumb that will cover all of the cases in the specifiication is that currency values need to be able to represent values one hundred times the largest value (including totals) that are directly based on the value.

Interestingly the current run on Zimbabwe Dollars has caused problems on a few of our internal applications. I'm guessing they might want to change that 100 figure pretty soon. A recent article on the run stated

At the time of writing this article, US$1 was worth $7 billion on the open market and the rate to the pound sterling was at $16 billion.

Scary

Mike

[georgev]

Originally Posted by blindman

MONEY is the root of all evil datatypes.

I thought that non-deterministic datatypes were the root of all evil?

[r937]

Originally Posted by Pat Phelan

[relevant stuff]

pat, i love u

[mike_bike_kite]

Originally Posted by georgev

I thought that non-deterministic datatypes were the root of all evil?

Can you explain the term with a few simple examples - I was too thick to understand the white papers that google returned

[georgev]

non-deterministic = approximate. E.g. float

[mike_bike_kite]

I'm still confused - I thought deterministic meant you always got the same result for the same supplied values. So the following Sybase functions are example is deterministic i.e. produce the same result each time:

select round( 123.456,1 ) -> 123.5

While the following functions are non-deterministic as the results will change each time you run them:

select rand()
select getdate()

Oddly though, if you supply a seed to the rand function then that suddenly changes it to deterministic. I then tried to apply all this to types to get a non-deterministic type but couldn’t. I'll agree that a float is an approximate value ie 1/3.0 will produce 0.333333 rather than a true 1/3 but it will always produce 0.333333. Was the issue more to do with the fact that internal size of a money type might vary between systems?

Mike

[Pat Phelan]

Mike:

I think that the issue that George was trying to explain was the fundamental difference between counting types and measuring types of numeric values. Unfortunatly if that is what he meant, MONEY actually IS a counting type so I'm confused by his comment.

Counting numeric types are ones that store how many of something. Counting types are exact, no rounding errors are inherant in the type itself so repeated operations on the largest and smallest possible values will always produce consistent results (no loss of precision). For most SQL databases these types are based on either integers or decimal data types.

Measuring numeric types are ones that store how much of something. They don't need to be exact, just fast, flexible, and precise within specified limits. Repeated operations on these types may or may not be consistent due to the acceptable "floating point error" which can accumulate, as well as the potential for slightly different computational results due to hardware differences. For most SQL databases, these types are based on floating point numbers.

From the mathematical purists point of view, counting types are truly deterministic because they will always produce the same values for the same computation. Numeric types are not necessarily deterministic since some operations may be meaningless (for example adding .01 to C which is rougly 3e8 generally results in a value equivalent to C).

If that isn't what George was getting at, I'm sure that he'll pipe up!

-PatP

[mike_bike_kite]

Originally Posted by Pat Phelan

From the mathematical purists point of view, counting types are truly deterministic because they will always produce the same values for the same computation. Numeric types are not necessarily deterministic since some operations may be meaningless (for example adding .01 to C which is rougly 3e8 generally results in a value equivalent to C).

A good description as always Pat but I still think determistic is getting mixed with approximate in the quote above. Deterministic should mean that the results are pre-determined by the inputs. So if 3e8 + 0.1 produces 3e8 every time it is run then doesn't that still make it deterministic? It's not a big point at all and I may well be wrong.

Mike