Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Round to Even (aka Banker''s Rounding) - The final function Expand / Collapse
Author
Message
Posted Tuesday, June 12, 2007 6:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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.
Post #373328
Posted Tuesday, June 12, 2007 7:01 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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.
Post #373329
Posted Tuesday, June 12, 2007 7:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,983, Visits: 31,509

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #373333
Posted Tuesday, June 12, 2007 7:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,983, Visits: 31,509

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #373334
Posted Tuesday, June 12, 2007 9:03 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 8, 2008 8:42 AM
Points: 775, 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.

Post #373347
Posted Tuesday, June 12, 2007 9:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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.
Post #373351
Posted Tuesday, June 12, 2007 11:31 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 36,983, Visits: 31,509

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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #373361
Posted Wednesday, June 13, 2007 1:32 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, March 30, 2008 9:53 PM
Points: 311, Visits: 1,918
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
Post #373374
Posted Wednesday, June 13, 2007 5:46 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, December 8, 2008 8:42 AM
Points: 775, 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.

Post #373433
Posted Wednesday, June 13, 2007 6:07 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
> 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?

Post #373440
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse