Re: rounding problems

Craig Ringer wrote:
> Justin wrote:
>
>
>> WE have several columns in table defined with numeric (20,10) thats is
>> just insanity.
>>
>
> Not necessarily. I have a few places where a monetary value is
> mulitiplied by a ratio quantity. For some of the historical data
> imported from another system the ratio can be irrational or at least not
> representable in any small precision value.
>
> I ended up needing a precision of 8 numeric digits to acceptably
> represent these ratios, resulting in a numeric(16,8) type to permit
> ratio values up to 99999999.99999999 . I probably could've got away with
> numeric(13,8) or even numeric(12,8) but as space and performance aren't
> utterly critical it didn't seem to be worth the risk of hitting limits
> and overflows later. As it is I'm tempted to go to 10 digits of
> precision, as there's still a 3 cent difference between the totals from
> the old system and the same data imported into the new system.
>
That 3 cent difference is over how many transactions ???

The differences i'm seeing are getting into the hundreds of dollars in 1
quarter within this stupid application.

The person/persons who laid this database out do not or did not
understand the compound rounding errors. I'm just trying to figure out
how best to fix it.

> You'll encounter similar situations in your materials consumption
> tracking (as you detailed below) and other places. So don't discount the
> use of high precision numeric values just yet.
>
> Personally I'd be tempted to use a `double precision' (float8) for
> things like materials consumption. Materials consumed in huge quantities
> will have lower price rates, and materials consumed in tiny quantities
> will often be priced higher. With wally-numbers: You're not going to
> care about the 0.0003 kg of steel consumed at a price of $0.00001 , but
> the same amount of something valuable might have a detectable (if still
> sub-cent) value. Floating point numbers are IMO better for that than BCD
> numeric. However, since the float will just get converted to numeric
> during multiplication with a numeric price-per-mass ratio it may well
> not be worth worrying about it.
>
> There's a use for that numeric(20,10).
>

I'm moving all the numeric fields to numeric(20,8) . I feel its pretty
safe with that scale setting. I agree data storage and performance
aren't critical concerns as they once were
>
>
>> Unless your doing scientific calculations which we do,
>> do. Having that many decimal points for an accounting package is just
>> nonsense and then its rounded to 4 or 6 in Inventory and Wip tables
>> then 2 when the numbers finally hit the GL tables. Who ever laid
>> these tables out has never had to try and get numbers to balance and
>> agree across tables :-( . Every time i dig a little deeper i keep
>> finding stupid things like this.
>>
>
> It sounds like you might have quite a bit of compounded rounding error
> from the successive stages of rounding as data moves through the system.
> Maybe you're rounding too aggressively?
>

Thats the problem the database layout is crap.
> I like to store a bit more precision than I have to, unless there's a
> business rule that requires rounding to a particular precision. For
> example, if your invoice items are rounded to whole cents you'd probably
> round the calculated invoice item price when inserting into an invoice
> item table.
>
> Of course, that means that
> sum(calculation of invoice item price)
> <>
> sum(rounded price of invoice items)
>
> because of rounding. That's fine; you can't balance the two things
> exactly because they're actually subtly different things. If you're
> using an appropriate rounding method for financial data, like
> round-to-even, you'll only ever get a couple of cents difference and
> that should be expected and ignored.
>
I normally would but given all the tables are showing different values
when summed over a Accounting period its adding up to significant
differences between all the tables.
>
>> Some people may think i'm crazy trying to track this down but when
>> you're only consume 0.003186 lbs of a metal per part that cost 22.7868
>> per lb and the work order calls fro 1148 parts. how the machine rounds
>> becomes a big problem (.00318611*1148) = 3.65765 lbs consumed *
>> 22.7868 = $83.3462 . Sense GL tables are 2 decimal the value is stored
>> as $83.35
>>
>
> Thinking about correct rounding and precision is very important, and far
> from crazy.
>
>
>> The question quickly becomes what number is the correct number.
>>
>
> Sometimes the answer is "both of them" - even though they are different.
> See the example above with rounded invoice items.
>
> --
> Craig Ringer
>