For example, when I'm processing sales and refunds I want a "symmetric" rounding method, that rounds the value of the refund to the symmetric of the rounded value of the sale, otherwise I wont have balanced accounts, and the customer might call me names

--
Fernando Dias
[Team Elevate]

Eric,

>The round function seem have problem on this situation :
>ROUND (5.625 TO 2) return 5.62. For me, the result must be 5.63
>ROUND (5.6251 TO 2) return 5.63. This is correct.
>I share some code with Oracle. With Oracle ROUND (5.625,2) return 5.63

Have you tried:

Round(Num+Sign(Num)*0.001)

where the 0.001 is one more digit than what you are rounding to.

Barry

Barry wrote:

Eric,

>>Have you tried:
>> Round(Num+Sign(Num)*0.001)
>>where the 0.001 is one more digit than what you are rounding to.

I should mention there is another way to "round" numbers with EDB.

select trunc(0.55, 1)  -- produces 0.5

select trunc(ColA+sign(ColA)*0.05, 1)  --Round to 1 decimal place and we get 0.6 if ColA=0.55

select trunc(ColA+sign(ColA)*0.5) --round to a whole number

The Trunc function accepts a second parameter that determines how many decimal places the number will be truncated to. So if we add 0.05 to a positive number it will "round" 0.55 to 0.6. This will eliminate any rounding peculiarities of the Round() function and gives you more control on how the rounding is done. This is an EDB extension and not a standard SQL function.

Barry

From the EDB SQL Manual:

TRUNCATE(<NumericExpression> [TO <IntegerExpression>])
TRUNCATE(<NumericExpression> [, <IntegerExpression>])
TRUNC(<NumericExpression> [TO <IntegerExpression>])
TRUNC(<NumericExpression> [, <IntegerExpression>])

Fernando

><< It depends on your definition of symmetric. >>
>
>The rounding method implemented by ROUND in EDB is what is called "round half away from zero", that is considered a symmetric method in the sense that globally it's not biased as the bias introduced for positive numbers compensates the bias introduced for negative numbers. Thats what I meant for "symmetric" rounding - and it's not *my* definition.
>Anyway, it seems it's not an issue for Eric as he just said he is using it only for positive numbers.

I wasn't getting at you "your definition" in this usage means "the definition currently selected for use in these particular circumstances" but takes less typing.

Roy

Fernando

>For example, when I'm processing sales and refunds I want a "symmetric" rounding method, that rounds the value of the refund to the symmetric of the rounded value of the sale, otherwise I wont have balanced accounts, and the customer might call me names

If I want a refund I want to be refunded what I was charged to start with (or preferably more than I was charged) <vbg>

More seriously if you're storing accounts information in a fashion that requires rounding I think you have a big problem anyway.

Roy

Roy,


<< if you're storing accounts information in a fashion that requires rounding I think you have a big problem anyway.
>>

Storing no (my customers would call me names again , but calculating the values to store, yes - because of taxes, discounts, unit conversions, currency conversions, salesmen commissions, royalties and all that stuff.
It's sometimes harder than it looks like to process partial refunds because of rounding errors - sometimes the sum of the partial refunds don't add up to the original sales total.

--
Fernando Dias
[Team Elevate]


 

Fernando


>Storing no (my customers would call me names again , but calculating the values to store, yes - because of taxes, discounts, unit conversions, currency conversions, salesmen commissions, royalties and all that stuff.

The best business solution - round down if its going out round up if its coming in

>It's sometimes harder than it looks like to process partial refunds because of rounding errors - sometimes the sum of the partial refunds don't add up to the original sales total.

My apologies - in my greed I hadn't thought of partial refunds.

Roy

Roy,

I like that method, I believe it's called "Round half towards my account"

--
Fernando Dias
[Team Elevate]

Eric,

<< The round function seem have problem on this situation :
ROUND (5.625 TO 2) return 5.62. For me, the result must be 5.63
ROUND (5.6251 TO 2) return 5.63. This is correct.
I share some code with Oracle. With Oracle ROUND (5.625,2) return 5.63 >>

Fernando brought this to my attention and was kind enough to even dig out
the solution for me, so a fix will be in 2.11 B2, which I'm working on right
now.  My guess is that it will be ready by tomorrow morning.

Tim Young
Elevate Software
www.elevatesoft.com