Is this SQL code susceptible to rounding errors?

Question

I have discovered some strange behavior where a stored procedure is returning inaccurate results by a penny or two.

Here's the code (I didn't write it):

ALTER  PROCEDURE [dbo].[TN_GetSimpleBalance] 
    @custID int,
    @nBalance decimal (8,2) output
AS

declare @ArBalance as decimal (8,2)
declare @custStatusCode varchar (2)
declare @unbilledCallsAmount as decimal (8,2)

set @nBalance = 0

set @ArBalance = 0
set @custstatusCode = ''
set @unbilledCallsAmount = 0


SET NOCOUNT ON 

    select @unbilledCallsAmount = isnull(sum(callcharge+taxamount),0) 
    from call with (NOLOCK) where custid = @custID and callstatuscode in ('R', 'B')

    --get AR balance
    select @ArBalance = isnull(sum(amount),0) 
    from artran with (NOLOCK) 
    where custid = @custID AND POSTEDFLAG ='Y'

    set @nBalance = @unbilledCallsAmount + @ArBalance

@nBalance is showing zero, even though another app is telling me the customer has $.02. callcharge and taxamount are both money datatypes.

This is the first time I've encountered this condition, but I am moving some related code to production and have been "asked" to research this.

What's your take? Is there weirdness going between money and decimal datatypes? Anything else you think might explain this?

Answer 1

What data types are callcharge and taxamount? I would do all my calculations in that data type and cast to decimal at the end. Currently all your intermediate vars are decimals which means that rounding errors compound. Don't round until the end.

Answer 2

I don't see any money datatypes in your code, but I assume call.callcharge & call.taxamount are? I don't know about the precision issue offhand.

Probably the best thing you can do is try to find a specific test case where data has this discrepancy, and see if you can reproduce it consistently. Then you can pick apart the logic a statement at a time and figure out where the discrepancy is introduced.

Is it possible you have a null callcharge or null taxamount? (null+(-$0.02)) => null; maybe

isnull(sum(callcharge+taxamount),0)

should be:

sum(isnull(callcharge, 0)+isnull(taxamount,0))

@unbilledCallsAmount or @ArBalance might also be null if those select statements return no records. HTH,

Answer 3

when using this, and trying different hard coded values for values callcharge & taxamount:

declare @DecimalAmount as decimal (8,2)
declare @MoneyAmount as money

select @DecimalAmount = isnull(sum(callcharge+taxamount),0) 
      ,@MoneyAmount   = isnull(sum(callcharge+taxamount),0) 
    from (select CONVERT(money,1.43) AS callcharge, CONVERT(money,.83) AS taxamount
          UNION select CONVERT(money,1.43) AS callcharge, CONVERT(money,.01) AS taxamount
          UNION select CONVERT(money,1.43) AS callcharge, CONVERT(money,.99) AS taxamount
          UNION select CONVERT(money,1.43) AS callcharge, CONVERT(money,.03) AS taxamount
         ) dt

select @DecimalAmount,@MoneyAmount

I could only get the values to differ if you have 3 or more decimal places in callcharge or taxamount. So if you don't have data like that you are ok.

Answer 4

Your money (from comment) columns are being converted to decimal(8,2) because of datatype precedence. And money-> numeric (near end) is rounded they are rounded

So both @unbilledCallsAmount and @ArBalance will be rounded and it's all cumulative.