Round to Even (aka Banker''s Rounding) - The final function
-
ROUND() sees numeric representation a value between 0.045000(0) and 0.045000(9).
In case of 0.666666 ROUND() sees, according to the same logic, numeric representation for a value between 0.666666(0) and 0.666666(9).
According to the precision given.
_____________
Code for TallyGenerator -
No, Round() gets a single value as its numeric expression, not some nebulous range between .0450000 and .0450009. Hell, if what you claimed was true, Round wouldn't even know what to do if you rounded it to 6 places. You keep asking us if we're programmers, and in fact, many of us are. Programmers will gladly tell you that functions don't get fuzzy parameters like you are describing.
Again, my question is, what is that single value that Round would receive, given your test setup, preferably to at least 15 digits to the right of the decimal point?
-
Yes, ROUND() gets single numeric expression which represents single value according to the precision specified.
If you would read math handbook in school you'd know that rounding a value having 6 digits precision to 6th digit is mathematically incorrect and returns occasional result.
This is the reason for making MONEY to hold 4 decimal digits, when you never need more than 2 to display.
2 spare digits are required to make sure that final rounding will take at least 1 true following digit and provide valid result.
If programmer does not know that it's not a programmer.
Such programmer should not be allowed to do programming at all.
And again, from my test setup: function receives a value represented by numeric expression 0.045000 having 6 decimal digits precision.
This expression with given precision represents any value between 0.045000(0) and 0.045000(9).
_____________
Code for TallyGenerator -
I didn't ask what it represented, I asked what Round() receives as a parameter. It receives a single number, accurate or otherwise. What is that number based on your test?
If what you said was true, then
CASE WHEN Round(.045000,6) > .045000 THEN 'True' ELSE 'False' END
would be True in almost all cases, as .0450001, .0450002, .0450003, .0450004, .0450005, .0450006, .0450007, .0450008, and .0450009 would all make that a True statement. Only .0450000 would be False. Yet, as we both know, that statement is never True, no matter how many times you execute it.
Again, exactly what numeric expression parameter would the Round() function receive in your test? It's a single number. You seem to have problems answering this one specifically, and I'm pretty sure it's because you don't like the answer.
-
Do you understand word "precision"?
You've got 6 precise digits.
That's it.
This is the limit you can see.
Nobody can tell anything about any further digit.
Because they are UNKNOWN.
They are BEYOND GIVEN PRECISION.
You've got an expression with LIMITED PRECISION.
LIMITED PRECISION.
Understand?
Ask some professionals if not.
So, you may be sure about 6 digits only. No more.
7th, 8th, etc. digits are NOT SPECIFIED.
Result of your test is incorrect by definition. Because the operation in mathematically incorrect.
And you cannot have there 0.0450001 - you're limited to 6 decimal digits precision.
Read some books how to build correct tests.
_____________
Code for TallyGenerator -
Oh, so Round() sees .045000?
Cool. Lynn, your function works great on Sergiy's test then, since it rounds .045000 exactly as expected, based on the rules used for Banker's Rounding. Still have that negative number issue, which is unrelated to this, but at least it passed his initial test. Nice work, Lynn!
-
Made another modification to my decimal version:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_BRound] (
@val decimal(38,20),
@pos int
)
RETURNS decimal(38,20)
as begin
declare @tmpval1 int,
@tmpval2 decimal(38,20),
@retval decimal(38,20),
@tmpval3 decimal(38,20),
@tmpval4 decimal(38,20)
set @tmpval1 = floor(abs(@val) * power(cast(10 as float), @pos))
set @tmpval2 = round(@val, @pos, 1)
set @tmpval3 = sign(@val) * (0.5 * power(cast(10 as float), (-1 * @pos)))
set @tmpval4 = (@val - @tmpval2)
set @retval = round(@val, @pos, case
when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null
and ((@tmpval3 >= @tmpval4 and sign(@val) = 1)
or (@tmpval4 >= @tmpval3 and sign(@val) = -1))
then 1
else 0 end)
return @retval
end
GO
Also, if you do the following you get Sergiy's test to a greater precision:
Declare @a decimal(38,20)
SET @a = cast(5 as decimal(12,3))/cast(111.111 as decimal(12,3))
select round(@A,2,0), dbo.fn_BRound(@A, 2), @a
Try it and see.
-
It does not matter what it sees.
What seen thing means - what matters.
0.045000 represents actually supplied value 5/111.111 with 6 decimal digits precision.
So, result of BR function is wrong, because following, not displayed digits are not all zeros.
And correct result must be 0.05.
THE VALUE SUPPLIED IS NOT PRECISE!!!
THE DATATYPE USED FOR THE VALUE IS NOT ABSOLUTELY PRECISE!!!
I don't believe anyone can be that dumb not to understand such simple things!
Why you prefer to show your stupidity rather than admit one mistake?
_____________
Code for TallyGenerator -
So, if using long division and I stop at 0.0450 (4 decimal positions of precision) and then do a bankers round to 2 decimal positions and round down to .04, I'm wrong? No. When SQL divides 5/111.111 and returns 0.045000, one may assume that all further digits after the last 0 are also zero. If you only give it 6 decimal digits to the right of the decimal, that is all it has to work with, wether it is the bankers round or traditional round function.
If anyone keeps changing the rules, Sergiy, it is you, not us. You have yet to prove that the Round to Even function is completely erronous and should never be used. It is a tool that may be used, and knowing how it works helps developers decide what tool to use and when, which comes down to making Business Decisions with the support of the business users.
-
> one may assume that all further digits after the last 0 are also zero.
Based on what?
You know that assumption is wrong.
When SQL divides 2./3 and returns 0.666666 - what are the next digits?
According to your logic we must assume the all are 0.
Would you say it's right assumption?
Everyone will say it's not.
I illustrated earlier, it's possible all following digits are "0" - with 0% probability.
That's what I told you from the very beginning:
BR is based on WRONG ASSUMPTION.
For the values where BR returns result different from TR it returns correct result with 0% probability.
So, you may be sure - it's 100% wrong result for those numbers.
That's why I insist it should not be used.
Unless user specifically have chosen wrong tool.
It's user's problems then.
_____________
Code for TallyGenerator -
And it is your choice not to use it. That doesn't mean that the rest of us has to live by your choice. We are free to make what ever choices we determine best meet the business requirements with the consent of the business users.
And with 2/3 = 0.66666, at that point I would assume further digits would also be 6's. Again, it is a decision based on what is known. If you actually take the time to look at the mathematics, you are actually limited to how far you can go in precision based on the precision of the values you are using. Once you reach that point, anything further, in your own words, is imprecise and questionable. So if your division of 5/111.111 is only valid to 6 decimals, then it is equal to 0.045000 and rounding to 2 decimal digits using the BR function will return 0.04.
-
> Again, it is a decision based on what is known.
Known is that:
0.666666
0.045000
What is your assumption about following digits for the values represented by these numbers?
_____________
Code for TallyGenerator -
Doesn't matter, as you you will say what ever you wish regarding my answers. Bottom line, prove to us mathematically that Round to Even is totally inaccurate. Showing us individual values, such as 5/111.111 is NOT a proof. For every number like 5/111.111, we can show you a number that works, which again isn't a proof, but it defeats your proof using individual values.
The reason you haven't shown us a valid mathematical proof, is because one does not exist.
-
Say goodnight Sergiy.
-
Mathematically:
if there is a number (one, single number) when it does not work - it does not work.
This is how it is.
To prove it works you need to prove there are no cases when it possible does not work.
And if someone have found one case when it does not work - all you proofs going down the drain.
Logical proof I posted many times:
BR is based on assumption of absolute, unlimited precision of supplied values.
There are no measuring or computing systems allowing absolute precision.
All values anyone/anything can possibly supply to this function have limited precision.
That's why BR cannot be right.
So, proof exists.
Even 2 of them.
Both are mathematical.
And you know, Lynn, your refusal to answer the question and quick "good night" speak better then any mathematical proof.
You avoided to answer not comfortable question.
You ran away from the battle field, refused to take a challenge, and now you gonna start telling people in your village how bravely you defeated your enemy.
If they are stupid enough they would probably believe you.
_____________
Code for TallyGenerator
Viewing 15 posts - 91 through 105 (of 377 total)
You must be logged in to reply to this topic. Login to reply