SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26320 Visits: 12488
Sorry, there was no comment about change in using of parameter.

I tried:

DECLARE
@enumerator DECIMAL(38, 12)
,@denominator DECIMAL(38, 12)

SELECT
@enumerator = 5
,@denominator = 111.111
SELECT
dbo.fn_BRound2(@enumerator/@denominator, 2)
,Round(@enumerator/@denominator,2)

Result:
.0400 .050000

Still need to try harder.
Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26320 Visits: 12488
Sorry David,
error was not on my side.

Error was in absence of documenting the changes in the function usage.
It's developer's fault, not user's.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221614 Visits: 42003

David,

Just making sure of a nuance to Banker's Rounding... it implies rounding to the nearest penny (hundreths) no matter how many decimal places may be involved... is that correct? (I know... a bit silly on my part but I just want to be 100% sure.)



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221614 Visits: 42003

David Jackson,

Does the function you posted work as described in the code or is there a problem with it... just trying to figure out where THIS thread is gonna go



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
David McFarland
David McFarland
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3025 Visits: 214

It can denote rounding to the nearest penny, but it's not really a banking specific function (it's actually not widely used by banks as far as I know, but far more often in applications like digital signal processing, etc.), so it can also round to the nearest thousandths, ten thousandths, even dollars, for that matter.

The only difference between it and traditional rounding is the handling of values exactly halfway between two possible results, so if rounding to the nearest penny, banker's rounding would kick in with given a given value of 1.045, but not 1.044999999999999, nor 1.0450000000000001. It has to be exactly halfway, otherwise, just use the Round function and you should get the same results.

There is another difference, but it's more implementational than a directive of the function. Many of the functions available allow you to round things to the nearest nickel (values not denoted by a single decimal position), for instance, which isn't an intended purpose of traditional rounding, even though it's possible to do so. I think that is probably due to the name of the function as much as anything, although with the push to nickels (and their equivalents) being the smallest currency in many places, it has gained some traction.


Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26320 Visits: 12488
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)SSC Guru (221K reputation)

Group: General Forum Members
Points: 221614 Visits: 42003

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Koji Matsumura
Koji Matsumura
Right there with Babe
Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)Right there with Babe (737 reputation)

Group: General Forum Members
Points: 737 Visits: 1918
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
David McFarland
David McFarland
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3025 Visits: 214

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.


Sergiy
Sergiy
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26320 Visits: 12488
> 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search