Bankers Rounding
-
ISFA = "In So Far As" if that's what you meant by "WTF does that mean?"... or did you have a different question, Cory?
Dunno where you came up with "PC Load Letter"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Where?
I saw truncated value .66666666, not 2/3.
Can you pass 2/3 = 0.(6) into that function?
_____________
Code for TallyGenerator -
BTW, I'm thrilled to see the implementation of BR which will round 100000000.01/800000000.01 correctly.
_____________
Code for TallyGenerator -
Legendary Thread!
Of course, after 28 pages I have to go back to the beginning now to remember what actually started this.
--Chris
-
Let me recap this for you using non technical terms
- I have a red paint but the color comes out pink.
- Add more of the red tint.
- Worked fine, thanks.
- You should never use red paint, it is bad.
- But I have to paint the room in red so I have to use the red paint. Red paint, for red color.
- No, green paint for green color. Red is bad.
- Red for red.
- No, green for green.
- I need red paint for red color.
- No, you need green paint to get green color.
.....
.....
- But the red color is what my wife wants.
- Your wife is wrong, paint green.
.....
- Can you prove you can paint green using the red?
- But I want red!!!
.....
And that's where we are...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Jeff, That comment is a direct reference to the movie Office Space, said to the fax machine that eventually becomes the target of total destruction in the field. The destruction happens 2/3 thru the movie, but we may need Sergey to help find that.
-- Cory
-
Now you're just being stupid (okay, that's actually not true, as you didn't just start with that post).
2/3 can't be passed to the built-in Round() function either, as the computer can't accurately represent .666... This has as much to do with banker's rounding as Cinderella's coach turning into a pumpkin at midnight has to do with it. If these sidetracks are intended to detract from the fact that you are wrong, they're ineffective.
Again, the computer's problem with handling certain rational numbers is a completely outside of the realm of this discussion, as it has no bearing on it. As noted multiple times, both banker's rounding and traditional rounding will return the same values for 2/3, given the same inputs, as banker's rounding will actually just implement traditional rounding to handle it, since it doesn't meet the very narrow criteria at which banker's rounding deviates from the other method, no matter how many digits you carry 2/3 out.
-
The round-to-even method, aka "Banker's rounding" among many other names, is an ASTM standard as noted in the following places:
http://exceltips.vitalnews.com/Pages/T1055_Rounding_Religious_Wars_Take_Two.html
http://en.wikipedia.org/wiki/Rounding
http://www.modot.gov/business/materials/pdf/vol_1/GS007.pdf (it's a PDF, but it's a governmental reference)
http://www.desc.dla.mil/DCM/Files/t83133e.pdf (also a PDF, but even the military finds it applicable at times, see page 4)
I'd happily include the text of the actual standard itself, but it costs money. Anyone who actually still believes Sergiy, feel free to cough up the 40 bucks or so and buy it.
ASTM, while it has the name "America" in its name, is actually an international organization, with over 30,000 members from over 100 countries.
Many organizations in Australia use the ASTM standards, as you can see here. Whether they use the "round to even" standard is something I could care less about, as that doesn't negate the fact that it is, one, a widely used standard, and two, a therefore necessary component in many SQL developer's toolboxes.
Game. Set. Match.
-
At this point, since I think I've provided convincing evidence that this is often required, whether you consider it complete crap or the holy grail, I'm going to start another thread, so we can actually work on fixing the problems with implementation. I'd really prefer that you don't shit all over that thread, Sergiy, so if you want to continue debating that you think it's crap, please do so here. I'd like to keep the other thread solely about fixing the function itself.
The new thread can be found here.
-
David,
the only person who demonstrates stupidity is you (not only, actually, but not me
).I passed to BR 2 representations of the same value 0.12514568:
with 3 digits precision and with 4 digits precision.
I've got different results from this function.
You told that those are different values and 0.125 cannot be considered as representation of 0.12514568.
Later you stated that for correct result you need to supply whole value, with all digits it handles, not its truncated version to BR.
Now I state that you cannot round 2/3 in BR. Just you cannot pass this value into that function.
And you are starting to to mumble about computer representations.
So,
WHY
0.666 is valid 3 digits precision representation of 2/3
but
0.125 is not valid 3 digits precision representation of 0.12514568
???
_____________
Code for TallyGenerator -
David, nobody can stop you from wasting your time on implementation.
Just take this for testing:
DECLARE
@enumerator DECIMAL(35, 25)
,@denominator DECIMAL(35, 25)
SELECT
@enumerator = 5
,@denominator = 111.111
SELECT @enumerator/@denominator,
dbo.fn_BRound(@enumerator/@denominator, 100)
,Round(@enumerator/@denominator,2)
And remember to add another "1" to @denominator when (if) you have done with these numbers.
_____________
Code for TallyGenerator -
JacekO, did you notice my post clarifiyng what was wrong with initial version of the function?
Post after which everything became clear?
Yes, I fixed the function, but I cannot fix wrong conception of this function.
So, iy it comes to "your wife", then just say this:
"yes I agree, the conception of the function is proven wrong, but this is "what my wife wants", so I have to use it, despite the fact i'll get sometims wrong results. But it's not my results, their accountants accept probability of those errors, so I don't care"
This would be perfect ending for this discussion.
P.S. My my statement about missing classes on 6th grade is still valid.
_____________
Code for TallyGenerator -
"I passed to BR 2 representations of the same value 0.12514568:
with 3 digits precision and with 4 digits precision.
I've got different results from this function."
As would be expected. A function will only operate on what is passed to it. 0 is also a representation of 0.12514568, as is .1, .12515, etc. I don't expect a function to return identical results for all of those. A function should operate on the value that it is passed, period, as it has no way to determine what the value was at some point prior.
"You told that those are different values and 0.125 cannot be considered as representation of 0.12514568.
Later you stated that for correct result you need to supply whole value, with all digits it handles, not its truncated version to BR."
I stated the same thing that I've stated all along. A function will operate on what it is passed. Round() does the exact same thing. If you call Round(0.12514568), it will return a different result than calling Round(0), even though they are both representations of the same number. Just because Round happens to return the same result whether you call it with 0.12514568 or with .12515 doesn't mean it is smart enough to know the original value. It's just operating on the two different values that happen to return the same result in this circumstance. Round(0.12515) doesn't magically know that the original value was 0.12514568.
"Now I state that you cannot round 2/3 in BR. Just you cannot pass this value into that function.
And you are starting to to mumble about computer representations."
No mumbling involved. .666... can not be properly represented by any of the numeric datatypes available to SQL Server. This has zip to do with Banker's rounding, as Round() has the exact same issue.
"So,
WHY
0.666 is valid 3 digits precision representation of 2/3"
It's not. The valid 3 digit precision representation of 2/3 is 0.667. Did the devil make you do that?
"but
0.125 is not valid 3 digits precision representation of 0.12514568
???"
It is a perfectly valid 3 digit precision representation of it. In fact, if you use banker's rounding to round 0.12514568 to 3 digits, it will return 0.125. Your problem is that you then expect that rounding 0.125 to 2 digits should return the same result as rounding 0.12514568 to 2 digits. Functions don't work that way, in any language, they work with the values they receive as parameters, not what those values once were. If you want Banker's rounding to round 0.12514568 to 3 digits properly, then pass it 0.12514568 (which is the number you're claiming that you want it to round), not 0.125.
This is as silly as rounding 0.12514568 to an integer (0), and then asking why Round(0, 2) doesn't return 0.13.
Again, and repeat after me, functions work with the values they are passed. Period.
-
Actually, you didn't "fix" the function, as it doesn't yet use a true "Banker's Rounding" algorithm. That's what the other thread is for.
You did, on the other hand, fix one of the problems with the function.
-
"So,
WHY
0.666 is valid 3 digits precision representation of 2/3"
It's not. The valid 3 digit precision representation of 2/3 is 0.667. Did the devil make you do that?
</I?
Really?
Did you see numbers on the screen?
Try again:
select 200./3
Show me where do you see any "7" in result.
Which value is represented by that result?
> Again, and repeat after me, functions work with the values they are passed. Period.
And you cannot pass 2/3 to this function. Never. No way.
Period.
_____________
Code for TallyGenerator
Viewing 15 posts - 271 through 285 (of 373 total)
You must be logged in to reply to this topic. Login to reply