Round to Even (aka Banker''s Rounding) - The final function
-
David,
if you are given with number 1.0450000000000001 (or 2/3) but you have only DECIMAL(18,6) storage for this, how can you produce right result from Bankers Rounding?
Can you post some code please?
And to see a resolution for the problem with 50/111.111 would be nice as well.
_____________
Code for TallyGenerator -
Ok... thanks David...
And I agree... Heh... after the last thread about this, I started asking all my CPA "friends" about it... out of 18 CPA's, only 2 of them even knew what Banker's Rounding was (none of them knew "round to even"), only 1 of them described it absolutely correctly, and none of them had ever used it except in class somewhere
And all of them use traditional rounding to the nearest dollar when preping taxes for folks and corporations because Uncle Sam expects it that way... even if they have a computer program do it for them 
Also talked with some banking "friends"... similar results. So, I tend to agree... "not really a banking specific function".That brings me to my next set of questions for anyone that cares to answer... (not directed at anyone in particular so no one get mad, please) with that in mind... and with the idea that it's more likely that Banker's Rounding will be used in "Digital Signal Processing" (although I don't see a sign of it in the Fast Fourier Transformation algorithyms on Wikipedia), can anyone think of a reason to actually have a Banker's Rounding function in SQL? What would we use it for? Perhaps in a more scientific oriented database (I tend to think of telephone call records and "banking functions" just 'cause I'm in a non-scientific area)? And, even if we did use it in the non-scientific world of call details and/or money, would a decent SOX auditor be capable of doing the rounding correctly? Would (s)he even know what the heck it is?
Or, is it all just "case in point" and a "whole lot of fun to figure/comiserate about"
No, I'm not trying to be a smart guy about this... I'm just thinking that it's such a rarely used form of rounding that most folks just don't even know what it is and I'm really curious if anyone actually uses it in SQL Server (or any RDBMS for that matter)... 
--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) -
A few years ago, I was developing a system using SQL 6.5/Delphi 3.
In front end (Delphi), default rounding was Banker's Rounding.
Because ANSI decided to uses Banker's Rounding as standard.
My client would not accept Banker's Rounding.
So I ended up writing a function in Delphi to use traditional rounding.
I know at least version 7 of Delphi still uses Banker's Rounding.
K. Matsumura
-
Jeff, it's actually a bit more commonly used than the anecdotal evidence would lead you to suggest, it's just poorly named. To be fair, it also has a ton of different names it goes by, which makes it more confusing. "Banker's Rounding", "Unbiased Rounding", "Statistician's Rounding", "Convergent Rounding", "Round To Even", "Round Half Even", "Round to Nearest", etc. On the other hand, it's also the rounding method used by millions of .NET apps, whether the users know it or not, since that is how Math.Round worked until 2005 (it still works that way, but now you can use traditional rounding if you'd prefer). It's the default rounding method per IEEE 754 (this is probably the biggest reason), .NET, several of the ASTM committees, Delphi, and many others. It's available in almost every programming language, Mathematica, Matlab, Excel, and countless other apps. In fact, SQL Server is one of the only places where I've needed it, yet it didn't exist natively. On the other hand, it wouldn't surprise me if Katmai supported it.
I've probably used it on only about 10% of the projects I've consulted on, and even then, I don't think all of them needed it (two of them were just "I heard this is good, so use it" type situations), but for those who do, it would be nice to have a working function. Basically, it's a tool. It could be indispensible to one person, and never picked up by another. I just think it would be nice for those who need it to have a good quality tool.
So, to answer your question, it's not universally used, but it's far more common than you'd think, so yes, I do think it's a wise component to have around.
-
> I just think it would be nice for those who need it to have a good quality tool.
So, what's a problem to prove the good quality of the tool on the examples posted 3 posts above?
_____________
Code for TallyGenerator -
Coming in on the tail end (of both threads) I thought I would try it this way
DECLARE @Result money,@Fraction int,@Interim float(53)
DECLARE @Digit int,@Counter int
SET @Result = CAST(FLOOR(@p1) as money)
SET @Interim = @p1 - @Result
SET @Fraction = 0
SET @Counter = 0
SET @Digit = 0
WHILE @Counter <= @p2
BEGIN
SET @Fraction = (@Fraction * 10) + @Digit
SET @Interim = @Interim * 10
SET @Digit = CAST(FLOOR(@Interim) as int) % 10
SET @Counter = @Counter + 1
END
SET @Fraction = @Fraction + CASE WHEN @Digit <= 4 THEN 0 WHEN @Digit >= 6 THEN 1 ELSE (@Fraction % 10) % 2 END
SET @Result = @Result + (CAST(@Fraction as money) / POWER(10,@p2))
SELECT @p1,@Result
Far away is close at hand in the images of elsewhere.
Anon. -
Very cool... Thanks for taking the time to explain in such great detail. Just for grins, I also checked Excel... the standard rounding functions don't appear to do Banker's Rounding...
Actually, these "disparities" between even just Microsoft systems don't surprise me a bit... just think of how many folks got the leap year calculation wrong for Y2K
--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) -
Was actually wondering when you'd show up on this, Mr. Burrows... you normally have some pretty good tricks up your sleeve
I'm going to have to give that one a try... thanks.--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) -
Was actually wondering when you'd show up on this, Mr. Burrows... Thanks Jeff
Boy was I tempted
Just decided to sit on the fence and watch the scrap
Far away is close at hand in the images of elsewhere.
Anon. -
Even Excel does it, but using a different function. Excel, due to how it is often used, has a ton of different rounding methods. One of them, "Even", is Banker's Rounding.
-
David Jackson,
I ran your latest function with the code below, and it passes the initial smell test, returning the values I would expect it to return for the test parameters I used (I commented what it should return, and got matches on all). More testing to do, but it does seem to handle passed doubles properly.
DECLARE
@pass1 DOUBLE PRECISION
,@pass2 DOUBLE PRECISION
,@pass3 DOUBLE PRECISION
,@pass4 DOUBLE PRECISION
,@pass5 DOUBLE PRECISION
,@pass6 DOUBLE PRECISION
,@RoundLevel int
SET @RoundLevel = 100
SELECT
@pass1 = .1249999999999999 -- should return .12
,@pass2 = .1250000000000000 -- should return .12
,@pass3 = .1250000000000001 -- should return .13
,@pass4 = .1349999999999999 -- should return .13
,@pass5 = .1350000000000000 -- should return .14
,@pass6 = .1350000000000001 -- should return .14
SELECT dbo.FN_BRound(@pass1, @RoundLevel)
SELECT dbo.FN_BRound(@pass2, @RoundLevel)
SELECT dbo.FN_BRound(@pass3, @RoundLevel)
SELECT dbo.FN_BRound(@pass4, @RoundLevel)
SELECT dbo.FN_BRound(@pass5, @RoundLevel)
SELECT dbo.FN_BRound(@pass6, @RoundLevel)
-
David, we all have already passed this track.
2 errors in your test:
1) you're performing implicit conversions without displaying its results;
2) you did not take for the taet values indicated earlier as a source of potential errors.
I fixed those errors.
Your function sucks.
DECLARE
@pass1 DOUBLE PRECISION
,@pass2 DOUBLE PRECISION
,@pass3 DOUBLE PRECISION
,@pass4 DOUBLE PRECISION
,@pass5 DOUBLE PRECISION
,@pass6 DOUBLE PRECISION
,@RoundLevel int
SET @RoundLevel = 100
SELECT
@pass1 = .1249999999999999 -- should return .12
,@pass2 = .1250000000000000 -- should return .12
,@pass3 = .1250000000000001 -- should return .13
,@pass4 = .1349999999999999 -- should return .13
,@pass5 = .1350000000000000 -- should return .14
,@pass6 = 612.945 -- should return 612.94
,@pass7 = 5.0250000000000000 -- should return 5.02
SELECT @pass1, dbo.FN_BRound(@pass1, @RoundLevel)
SELECT @pass2, dbo.FN_BRound(@pass2, @RoundLevel)
SELECT @pass3, dbo.FN_BRound(@pass3, @RoundLevel)
SELECT @pass4, dbo.FN_BRound(@pass4, @RoundLevel)
SELECT @pass5, dbo.FN_BRound(@pass5, @RoundLevel)
SELECT @pass6, dbo.FN_BRound(@pass6, @RoundLevel)
SELECT @pass7, dbo.FN_BRound(@pass7, @RoundLevel)
_____________
Code for TallyGenerator -
Damn, but you're a thread shitter. Oh, your test doesn't work.
Now, with that said...
David Jackson, while the problem isn't your's, but SQL Server's, I'm thinking my original concept of having a decimal version of this function is a good idea. Floats are crappily handled in SQL Server (computers in general, but SQL Server sucks worse than most), as can be easily demonstrated by fixing his code (just add an extra line in the DECLARE for the missing @pass7 declaration), and doing a SELECT @pass6, @pass7, which will demonstrate that, one, there is an issue, and two, the issue has nothing to do with your function.
The problem also affects the internal Round function as well, as can be seen by running the following:
DECLARE
@Pass1 DOUBLE PRECISION
,@RoundLevel int
SELECT
@Pass1 = 2.2649999999999999999999999999999999999
,@RoundLevel = 2
SELECT
@Pass1
SELECT
Round(@Pass1, @RoundLevel)
-
It does not affect ROUND function.
ROUND perfectly rounds the value you supplied:
@Pass1 = 2.2650000000000001
ROUND(@Pass1,2) = 2.27.
Absolutely valid result.
Sorry to tell you, but you cannot have absolutely precise numbers in computers.
Every you "precise" value is just shortened (truncated or rounded, depending on implementation) option of the FLOAT value actually stored in computer.
Find the trend of number of bytes used for every DECIMAL datatype depending on number of precise digits specified.
Compare it with number of bytes taken by REAL or FLOAT values having same or better precision.
P.S. Don't need to prove me that values you use are not precise.
I told you this from the very beginning.
Explain it to yourself.
_____________
Code for TallyGenerator -
No, the value I passed was "2.2649999999999999999999999999999999999" (it's right there in the code, and really hard to miss), which should round down to 2.26 using the ROUND(x,2) function, but instead rounds up to 2.27.
Please, please, please make the argument that the value the function received was different from what I passed, and therefore the Round() function is not responsible for the error.
Viewing 15 posts - 16 through 30 (of 378 total)
You must be logged in to reply to this topic. Login to reply