Round to Even (aka Banker''s Rounding) - The final function

Question

Round to Even (aka Banker''s Rounding) - The final function

Viewing 15 posts - 76 through 90 (of 378 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Which is not a problem with the function itself but with how SQL Server handles the 5/111.111, so get off it.

David McFarland SSChampion Points: 11815 More actions · Answer 2

That has nothing to do with Lynn's function. Round() also sees the result of your input parameters as .045, followed by nothing but zeroes as far as the eye can see.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

This is the problem of the function.

This is the problem of the assumption it's based on.

You can pass only limited number of values to it and YOU CANNOT PASS TO IT INFINITE NUMBER OF VALUES.

So, it works with 0.00(0)% of real world situations.

I would consider it useless.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 4

Lynn, I did find one problem so far, and that is with negative numbers. The following should round to -6.26, but rounds to -6.27. While I realize that it seems like Banker's rounding rounds down after even digits, thus making this correct, it actually is supposed to round to the nearest even digit.

Declare

@a decimal(38,20)

SET

@a = -6.26500000000000000

SELECT

[dbo]

.[fn_BRoundLynn] (@a, 2)

David McFarland SSChampion Points: 11815 More actions · Answer 5

Again, it sees the results of that division exactly the same as the internal Round() function, even though it returns a different result (it's supposed to), so I'm perfectly fine with that particular result to your test.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

David, yes, ROUND gets the same value, but it takes into consideration that result of division presented with precise value is always <= actual result of division which is with 100% probability imprecise value.

That's why it rounds it correctly.

BR makes false assumptions and returns wrong result.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 7

"If you cannot succeed change the requirements."

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 8

Succeeding fine. Based on the value it receives, it returns exactly what we expect it to (except for the negative number entries). Since it receives the exact same value that the Round() function receives, it's the fault of the system passing the value, which passes the exact numeric .045 to both functions, Lynn's and Round().

Sergiy SSC Guru Points: 110208 More actions · Answer 9

[dbo].[fn_BRound2] (-612.945, 2)

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 10

You did not succeed in any way with 5/111.111.

Result is incorrect.

And you cannot get correct result because the function does not work with imprecise values.

And every value is with 100% probability imprecise.

P.S. Once again, for extremely talented.

System does not pass exact numeric .045. It passes numeric representation for supplied value.

There is no any system fault. System does its job perfectly.

0.045 is proper representation of 5/111.111 with specified precision.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 11

"System does not pass exact numeric .045. It passes numeric representation for supplied value.

There is no any system fault. System does its job perfectly.

0.045 is proper representation of 5/111.111 with specified precision."

No, the system does pass the exact numeric .045 to Lynn's function, and the internal Round() function would also receive the exact numeric .045, per your test setup. I'm quite aware that 5/111.111 isn't exactly .045, but that doesn't change the fact that both Lynn's function and Round() would receive that exact same value from SQL Server based on your test conditions.

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Sorry, David,

but due to limited precision any computing system cannot pass exact representation for all values.

That's why all computing system use representations for actual non-precise valuers. Precision of the representation is based on the precision supported by computing system.

0.045000 is valid representation of 5/111.111 with REAL datatype precision.

Representation 0.045000 means that actual value lays somewhere between 0.045000 and 0.045001, according to given precision.

Valid computer function must work accordingly.

And ROUND() does.

BR does not by design. That's why it's faulty by definition.

Simple as that.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 13

You can't even stay on topic.

Does or does not the built-in Round() function receive the exact numeric .045 (with nothing but zeroes following), just as Lynn's function does, versus the actual result of 5/111.111 when using the parameters of your test that you used to test Lynn's function? A simple "Yes, it does" or "No, it doesn't" will suffice. If your answer is "Yes, it does", then Lynn's function performs exactly as expected.

Sergiy SSC Guru Points: 110208 More actions · Answer 14

My answer is "No, it does not".

Because datatype specified for the parameter has limited precision.

LIMITED PRECISION.

Absolutely precise value cannot be passed into the function.

Because all DECIMAL datatypes don't support absolutely precise values.

Sorry if I disappointed you.

But you could find this earlier for example in BOL.

_____________
Code for TallyGenerator

David McFarland SSChampion Points: 11815 More actions · Answer 15

Then tell me what the built-in Round() function would receive from the test setup you used for Lynn's BRound function. You can limit it to say 15 digits to the right of the decimal place, if you'd like, or you can go out further. That's your call.. Just tell me what Round() sees as its parameter in your earlier scenario.