money and accuracy

What's the best way to avoid the problem of being off by a cent or 2 in
money calculations due to internal floating point rounding? Does using the
"money" datatype solve this alone? Do you need to use all "integer" data
types insetad and handle displaying the data appropriately manually? I don't
want to end up with reports where the grand total at the end of a column of
numbers ends up different then if you were to figure it out on a pocket
calculator. What's the best strategy for this?
Thanks,
KeithDon't use float or double datatypes for currency values !
There is a specific datatype (Called money, or for less range and
precision, smallmoney) that is specifically designed for currency values,
that does not have this issue.
"Keith G Hicks" wrote:

> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Sorry - didn;t read yr entire post unti lafter Isent last ... Yes, to answe
r
your second question - Money solves this alone.
And No, to yr 3rd ? YOu don;t need to use integral datatypes...
"Keith G Hicks" wrote:

> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using th
e
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I don
't
> want to end up with reports where the grand total at the end of a column o
f
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>
>|||Keith
I'd recommend you using DECIMAL(18,3) datatype fo such things. For more
details please refer to the BOL
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OxXTufzJFHA.2468@.TK2MSFTNGP10.phx.gbl...
> What's the best way to avoid the problem of being off by a cent or 2 in
> money calculations due to internal floating point rounding? Does using
the
> "money" datatype solve this alone? Do you need to use all "integer" data
> types insetad and handle displaying the data appropriately manually? I
don't
> want to end up with reports where the grand total at the end of a column
of
> numbers ends up different then if you were to figure it out on a pocket
> calculator. What's the best strategy for this?
> Thanks,
> Keith
>|||Would it also then make sense do convert numbers to integers when doing
calculations and then convert them back to decimal values when posting the
results back to the table or displaying results in calculated grid columns
or reports? This made a lot of sense to me.
Amount Each Number Purchased Total
$1.23 1000 $1230.00
$0.57 2000 $1140.00
$2.13 1500 $3195.00
----
4500 $7620.00
We all know that the end result if there are a lot of rows could be 7619.99
or 7620.02 (or some other value close to the correct total). In fact I've
seen results where $1.23 * 1000 = 1230.01. But by converting $1.23 to 123
before multiplying by 1000 and then converting 123000 to 1230.00 and also
doing the same thing for the grand total you would end up with 100% accuracy
at all times.
Is there a better way to handle this? Some applications and clients do not
demand that kind of accuracy. But some do. What I'm currently working on
does. Is there a better solution to the problem? I've heard some developers
don't even use decimal values in teh back end for storing money. They use
integers only and then display decimals where needed to avoid the entire
problem altogether. That sounds too complex.
Any other suggestions would be welcomed.
Keith :)|||> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.

> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.

> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||Thanks for the input David.
What about on the front end? I see your point if calculations are taking
place in a trigger or a stored procedure but what about data types in a
front end like VB or Delphi? Oftentimes calculations need to be done on the
front end. Does it matter what data type is used there? Regardless of what
datatype the actual column in the table is defined as, I assume the front
end datatype would have an impact as well.
Also, according to BOL, Numeric and Decimal are identical. Is this not
actually the case?
Keith
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> We all know that the end result if there are a lot of rows could be
7619.99
> or 7620.02 (or some other value close to the correct total)
If you use inexact numerics such as FLOAT or REAL then that can happen,
yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.

> In fact I've
> seen results where $1.23 * 1000 = 1230.01
With what datatype? Not with NUMERIC.

> I've heard some developers
> don't even use decimal values in teh back end for storing money. They
use
> integers only and then display decimals where needed to avoid the
entire
> problem altogether. That sounds too complex.
None of that is necessary. You can experience rounding problems with
SQL Server's MONEY type because the result of a division may be a MONEY
with 4 decimal places of precision. That's why I repeat, NUMERIC is the
best type for currency amounts in most cases IMO.
If you are using NUMERIC and you still think you have a problem with
rounding then please come back with some code that will actually
reproduce the incorrect result you are getting.
David Portas
SQL Server MVP|||> Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
Definitely. Do as much calculations as possible in SQL Server, and know your
dev tool datatypes and
select carefully!

> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
They are the same. Did you find something in the thread indicating something
else? (I browsed
through the thread and couldn't find such statement.)
They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine c
an give a higher
precision than asked for for DECIMAL. For numeric, you get what you ask for
(which is how SQL Server
does it for both DEC and NUM).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Keith G Hicks" <krh@.comcast.net> wrote in message news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx
.gbl...
> Thanks for the input David.
> What about on the front end? I see your point if calculations are taking
> place in a trigger or a stored procedure but what about data types in a
> front end like VB or Delphi? Oftentimes calculations need to be done on th
e
> front end. Does it matter what data type is used there? Regardless of what
> datatype the actual column in the table is defined as, I assume the front
> end datatype would have an impact as well.
> Also, according to BOL, Numeric and Decimal are identical. Is this not
> actually the case?
> Keith
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1110720541.051228.117300@.g14g2000cwa.googlegroups.com...
> 7619.99
> If you use inexact numerics such as FLOAT or REAL then that can happen,
> yes. Don't use FLOAT or REAL for currency amounts. Use NUMERIC instead.
>
> With what datatype? Not with NUMERIC.
>
> use
> entire
> None of that is necessary. You can experience rounding problems with
> SQL Server's MONEY type because the result of a division may be a MONEY
> with 4 decimal places of precision. That's why I repeat, NUMERIC is the
> best type for currency amounts in most cases IMO.
> If you are using NUMERIC and you still think you have a problem with
> rounding then please come back with some code that will actually
> reproduce the incorrect result you are getting.
> --
> David Portas
> SQL Server MVP
>|||> What about on the front end? I see your point if calculations are
taking
> place in a trigger or a stored procedure but what about data types in
a
> front end like VB or Delphi?
The results would obviously depend on how your language maps SQL's data
types onto its own. In an N-Tier application you should expect to do
all or most of your calculations in the data tier.

> Also, according to BOL, Numeric and Decimal are identical.
Yes, in SQL Server they are the same. As I suggested before, it would
be easier to understand your problem if you could post a working
example in TSQL. If your problem is in another language then you will
probably get more help posting to a more appropriate group.
David Portas
SQL Server MVP
--|||Just as an fyi, when looking at the money vs. float values in SQL Server,
money and decimaly do what a lot of people consider "normal rounding".
Basically, the .5 gets rounded up. Everything else is rounded down. The
float datatypes in SQL Server appears to use bankers rounding. Here is an
example:
DECLARE
@.deceven DECIMAL(4,3),
@.decuneven DECIMAL(4,3),
@.floateven FLOAT,
@.floatuneven FLOAT,
@.moneyeven MONEY,
@.moneyuneven MONEY
SELECT
@.deceven = 2.425,
@.decuneven = 2.435,
@.floateven = 2.425,
@.floatuneven = 2.435,
@.moneyeven = 2.425,
@.moneyuneven = 2.435
SELECT
ROUND(@.deceven,2),
ROUND(@.decuneven,2),
CAST(ROUND(@.floateven,2) AS DECIMAL(4,2)),
CAST(ROUND(@.floatuneven,2) AS DECIMAL(4,2)),
CAST(@.moneyeven AS DECIMAL(4,2)),
CAST(@.moneyuneven AS DECIMAL(4,2))
Not that anyone cares. :) You can lookup banker's rounding if you don't
know what it means. Also, remember that float and real are floating point
datatypes, so they should only be used when you know for a fact you need
floating point numeric data for calculations. Also, rounding between
different versions of SQL Server with floats and reals are different. blah,
blah, blah, blah, blah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unBTEHAKFHA.336@.TK2MSFTNGP09.phx.gbl...
front
> Definitely. Do as much calculations as possible in SQL Server, and know
your dev tool datatypes and
> select carefully!
>
> They are the same. Did you find something in the thread indicating
something else? (I browsed
> through the thread and couldn't find such statement.)
> They are not exactly the same in ANSI SQL, though. In ANSI SQL, the engine
can give a higher
> precision than asked for for DECIMAL. For numeric, you get what you ask
for (which is how SQL Server
> does it for both DEC and NUM).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23SbC6I9JFHA.2212@.TK2MSFTNGP12.phx.gbl...
the
what
front
>