Amount rounding - What did I do wrong?

Question

Amount rounding - What did I do wrong?

Ken at work

Hall of Fame

Points: 3426
More actions
August 18, 2015 at 2:24 pm

#320738

I have this code:
IsNull(c.BeginBal,0) + IsNull(d.NetActivity,0) As [Begin_Bal]
It seems simple enough. Both are coming from Temp Tables with real as the data type.
I've tried:
IsNull(c.BeginBal,0) + IsNull(d.NetActivity,0) As [Begin_Bal]
IsNull(c.BeginBal,0.00) + IsNull(d.NetActivity,0.00) As [Begin_Bal]
Cast(IsNull(c.BeginBal,0.00) + IsNull(d.NetActivity,0.00) As real) As [Begin_Bal]
Cast(IsNull(c.BeginBal,0.00) As real) + Cast(IsNull(d.NetActivity,0.00) As real) As [Begin_Bal]
But no matter what it comes out as:
BeginBal = 1676559.81
NetActivity = Null
Sum = 1676560
Why is it rounding? Yes I tried having them in separate columns too, so I can see the amounts.
Any help would certainly be appreciated. I'm sure I'm doing something foolish.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

assuming both datatypes ARE real, cast the hard-coded values in the isnulls to the same datatpye - not the result of the isnull.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

TheSQLGuru (8/18/2015)
assuming both datatypes ARE real, cast the hard-coded values in the isnulls to the same datatpye - not the result of the isnull.

I'm not sure what you mean? Something like this?

Cast(c.BeginBal As real) + Cast(d.NetActivity as real) As [Begin_Bal]

This just throws a Null since NetActivity is null.

Ken at work Hall of Fame Points: 3426 More actions · Answer 3

I found it....

The values are not real they are numeric(9,2). I'm still not sure why they were rounding but at least this fixes it.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Ken at work (8/18/2015)
TheSQLGuru (8/18/2015)
assuming both datatypes ARE real, cast the hard-coded values in the isnulls to the same datatpye - not the result of the isnull.
I'm not sure what you mean? Something like this?
Cast(c.BeginBal As real) + Cast(d.NetActivity as real) As [Begin_Bal]
This just throws a Null since NetActivity is null.

IsNull(c.BeginBal,cast(0 as correctdatatype)) + IsNull(d.NetActivity,cast(0 as correctdatatyp)) As [Begin_Bal]

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Sergiy SSC Guru Points: 110208 More actions · Answer 5

TheSQLGuru (8/18/2015)
IsNull(c.BeginBal,cast(0 as correctdatatype)) + IsNull(d.NetActivity,cast(0 as correctdatatyp)) As [Begin_Bal]

Or -

Declare @DecimalZero decimal (9,2)

SET @DecimalZero = 0

SELECT IsNull(c.BeginBal, @DecimalZero) + IsNull(d.NetActivity, @DecimalZero) As [Begin_Bal]

_____________
Code for TallyGenerator

Ken at work Hall of Fame Points: 3426 More actions · Answer 6

Wow great ideas. I've never thought of that.

Thank you,

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

IMPLICIT CONVERSIONS can be a REAL shock and PAIN-CAUSER in SQL Server. The alternative is getting the wrong answer with no warning or error (which is by FAR the worse thing that can happen in any data processing), so I suppose they are a necessary evil! 🙂

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

TheSQLGuru (8/18/2015)
Ken at work (8/18/2015)
TheSQLGuru (8/18/2015)
assuming both datatypes ARE real, cast the hard-coded values in the isnulls to the same datatpye - not the result of the isnull.
I'm not sure what you mean? Something like this?
Cast(c.BeginBal As real) + Cast(d.NetActivity as real) As [Begin_Bal]
This just throws a Null since NetActivity is null.
IsNull(c.BeginBal,cast(0 as correctdatatype)) + IsNull(d.NetActivity,cast(0 as correctdatatyp)) As [Begin_Bal]

This is nonsense. ISNULL function will use the datatype of the first value. So casting the second value will be redundant and might cause additional conversions when the data type is different from the first value. It would make sense for values that might need format codes for conversion.

Note that this behavior is different in COALESCE, which uses data type precedence rules to choose the data type.

The main problem was that you were using real which is an approximate numeric data type instead of decimal which is an exact numeric data type.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

Ken at work (8/18/2015)
I found it....
The values are not real they are numeric(9,2). I'm still not sure why they were rounding but at least this fixes it.

I still don't know how BeginBal = 1676559.81 became Sum = 1676560 with numeric(9,2) data types. It should have still been 1676559.81 with the conversion that Louis (and BOL) says should happen automatically...

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 10

Yes, if they were both numeric(9,2) then there should not have been any rounding. If either value were real instead of numeric(9,2), then the result would make sense, because the precision of real is only 7 digits.

To wit:

DECLARE @firstNumeric NUMERIC(9,2)=1676559.81

DECLARE @secondNumeric NUMERIC(9,2)

DECLARE @real REAL

SELECT ISNULL(@firstNumeric,0)+ISNULL(@secondNumeric,0)

SELECT ISNULL(@firstNumeric,0)+ISNULL(@REAL,0)

Are you absolutely certain both values were numeric(9,2)?

Cheers!