Banker''s rounding in T-SQL (like Math.Round in .NET)
-
Sorry, Jeff, but the use of STR does not work as the expected results are incorrect. With Banker's Rounding to 2 decimal points:
For 3.455, since the third decimal position value is 5 and the second decimal position is odd, this should truncate, not round. The expected results should be 3.45 not 3.46
SQL = Scarcely Qualifies as a Language
-
Carl wrote: "For 3.455, since the third decimal position value is 5 and the second decimal position is odd, this should truncate, not round. The expected results should be 3.45 not 3.46"Not true, Carl... you need to go back and read about Bankers Rounding in the original post from Andrew... the following is a copy of part of that post to refresh your memory... I've highlighted the important part so you can find it
Examples of banker's rounding (results of Math.Round in .NET):Math.Round(3.454,2) -> 3.45
Math.Round(3.455,2) -> 3.46Math.Round(3.445,2) -> 3.44
Math.Round(3.456,2) -> 3.46
According to what Andrew has stated in other posts, if the number ends in precisely 5 mils, then the number is supposed to be rounded to the nearest even hundreth.
{Edit}... Sorry folks... I posted some bad info... Serqiy figured out the error... I mistook rounding of the underlying FLOATS for Bankers' Rounding
--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)
Intro to Tally Tables and Functions -
Andrew, you're making a classic error... you are doing traditional rounding to 3 places before you are attempting the bankers rounding....Just like the example of 3.445657545 being rounded to 3.45 instead of 3.44 because of the digits that follow the mils position, so must you consider the digits following the mils of 3.4546... since the original does NOT precisely end with 5 mils, Bankers Rounding is not applicable in this instance. At least according to your original definition of Bankers Rounding... here's some code the demonstrates the proper consistency of the Bankers Rounding associated with STR...--===== If test table exists, drop itIF OBJECT_ID('TempDB..#MyTemp') IS NOT NULL
DROP TABLE #MyTemp
--===== Create the test tableCREATE TABLE #MyTemp(Number DECIMAL (38,20))
--===== Populate the test table with dataINSERT INTO #MyTemp (Number)
SELECT 3.4600 UNION ALL
SELECT 3.4590 UNION ALL
SELECT 3.4580 UNION ALL
SELECT 3.4570 UNION ALL
SELECT 3.4560 UNION ALL
SELECT 3.4550 UNION ALL
SELECT 3.4549 UNION ALL
SELECT 3.4548 UNION ALL
SELECT 3.4547 UNION ALL
SELECT 3.4546 UNION ALL
SELECT 3.4545 UNION ALL
SELECT 3.4544 UNION ALL
SELECT 3.4543 UNION ALL
SELECT 3.4542 UNION ALL
SELECT 3.4541 UNION ALL
SELECT 3.4540 UNION ALL
SELECT 3.4530 UNION ALL
SELECT 3.4520 UNION ALL
SELECT 3.4510 UNION ALL
SELECT 3.4500 UNION ALL
SELECT 3.4490 UNION ALL
SELECT 3.4480 UNION ALL
SELECT 3.4470 UNION ALL
SELECT 3.4460 UNION ALL
SELECT 3.4450 UNION ALL
SELECT 3.4449 UNION ALL
SELECT 3.4448 UNION ALL
SELECT 3.4447 UNION ALL
SELECT 3.4446 UNION ALL
SELECT 3.4445 UNION ALL
SELECT 3.4444 UNION ALL
SELECT 3.4443 UNION ALL
SELECT 3.4442 UNION ALL
SELECT 3.4441 UNION ALL
SELECT 3.4440 UNION ALL
SELECT 3.4430 UNION ALL
SELECT 3.4420 UNION ALL
SELECT 3.4410 UNION ALL
SELECT 3.4400
--===== Demonstrate the ProblemSELECT Number AS Original,
STR(Number,10,2) AS Bankers,
ROUND(Number,2) AS Traditional
FROM #MyTemp
{Edit}... Sorry folks... I posted some bad info... Serqiy figured out the error... I mistook rounding of the underlying FLOATS for Bankers' Rounding
--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)
Intro to Tally Tables and Functions -
Jeff, you are right - STR behaves like Math.Round in .NET. But I found one exception I can't explain:
In .NET:
Math.Round(40.645161290322584,2) --> 40.65
Math.Round(41.645161290322584,2) --> 41.65
Math.Round(40.645,2) --> 40.65
Math.Round(41.645,2) --> 41.64
STR in SQL:
select str(40.645161290322584, 10, 2) --> 40.65
select str(41.645161290322584, 10, 2) --> 41.65
select str(40.645, 10, 2) --> 40.65
select str(41.645, 10, 2) --> 41.65
If someone knows HOW IT ROUNDS digits that follow the mils position - please explaine me - I'm absolutely confused with it.
-
Also, I've found "banker's" round in VB from Microsoft
http://support.microsoft.com/default.aspx?scid=kb;EN-US;196652
I've translated it to T-SQL, here it is:
create function BankRoundDotNet (@val as numeric(30,15), @factor as int)
returns money
as
begin
declare @result double precision
declare @temp as double precision,
@fix_temp as double precision
set @temp = @val * @factor
set @fix_temp = floor(@temp + 0.5 * sign(@val))
if (@temp - cast(@temp as int) = 0.5)
begin
if ((@fix_temp / 2) cast(@fix_temp / 2 as int))
begin
set @fix_temp = @fix_temp - sign(@val)
end
end
set @result = @fix_temp / @factor
return @result
end
-
I've investigated it: seems like that's due to inaccuracy with double - very small amounts (like 0.0000000005) are added while calculating. So, I think it' s problem with double calcualations.
I need to explain "banker's" rounding to QA staff. How can I explain it in a simply?????
-
Easy...
1. Any decimal dollar amount greater than x.xx5, regardless of the number of decimal places, will be rounded up to the penny.
2. Any decimal dollar amount less than x.xx5, regardless of the number of decimal places, will be rounded down to the penny.
3. Any decimal dollar amount precisely equal to x.xx5 (trailing zeroes allowed), will be rounded to the nearest even numbered penny.
--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)
Intro to Tally Tables and Functions -
Math is one of my weaknesses and some of you seem good at it. This is a routine I inherited and use in production. It is to round from four places to two and should be "banker's rounding". Is it flawed and if so which of the ones posted which would be "best"?
update #IntermediateBR set paidamount = ROUND(paidAmount, 2)
where convert(bigint, paidamount * 10000)%100 > 0
-
Tyson, as I see your script is not using banker's rounding. ROUND - uses traditional arithmetic (5 always is rounded up).
If you are rounding only on DB level it's better to use STR function (see posts of Jeff Moden).
But if you also round in .NET code it's better to use BankRoundDotNet (posted by myself). It behaves equally to Math.Round in .NET (with the same bugs
)That's my humble opinion.
-
Hey folks... I screwed up
way back when and I've crossed out my posts about this because they were wrong... what I saw as Banker's Rounding was actually rounding of underlying FLOAT's... the STR function does NOT do Bankers' Rounding as I previously stated (Serqiy showed me about the FLOAT thing).That's what I get for using such a narrow test range... seemed to work fine there but doesn't work in other places on the number line.
I know it's almost a year after these original posts, but I wanted to make sure that no one else was misled by the bad info I posted. And my most sincere appologies to those folks who used that bad info.
--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)
Intro to Tally Tables and Functions -
Another implementation that I just wrote:
CREATE FUNCTION BANKROUND(@val as money) RETURNS money AS BEGIN DECLARE @nonsig AS money DECLARE @newval AS money DECLARE @changesign AS bit IF @val 0.005 OR (@nonsig = 0.005 AND @newval % 0.02 = 0.01) SELECT @newval = @newval + 0.01 IF @changesign = 1 SELECT @newval = -@newval RETURN @newval ENDThis only works for money data types at present, but it does avoid any floating point calculation and potential overflow issues with multiplying by 100. I think it could be modified to support decimal data rounded to @n decimal places as well by modifying the 0.005, 0.01 and 0.02 hard coded numbers to be variables 5 / 10^(@n+1), 1 / 10^@n, and 2 / 10^@n, respectively.
-- Jason Carter
-
Thanks Carl...
--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)
Intro to Tally Tables and Functions -
Carl,
Your RoundBanker does NOT implement Banker's Rounding correctly.
Because people seem to be using your implementation, I thought I should post this message to warn them.
The problem is that your understanding of Banker's Rounding is incorrect. It does NOT apply from right to left.
According to Wikipedia, these are the steps:
[font="Courier New"]1. Decide which is the last digit to keep.
2. Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.
3. Leave it the same if the next digit is 4 or less
4. Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes; then change the last digit to the nearest even digit. That is, increase the rounded digit if it is currently odd; leave it if it is already even.[/font]
In other words, the only case Banker's Rounding behaves differently from the Arithmetic Rounding is when the part to be rounded is exactly halfway: 0.00500000000... (if you are rounding to a penny).
0.005000001 must round to a penny: 0.01
SELECT dbo.RoundBanker(0.005000001, 2) returns 0.0, which is incorrect.
You wrote:
Start with 3.4546
Banker's Round at 3 gives 3.455
Banker's Round at 2 gives 3.46
Second Pair:
Start with 3.4653
Banker's Round at 3 gives 3.465
Banker's Round at 2 gives 3.46
3.4546 rounded to 2nd decimal must give 3.45
3.4653 rounded to 2nd decimal must give 3.47
-
I started this thread 2 year ago. And it still seems to be actual.
For all this time I'm using the following funtions for banker's rounding and seems it work great:
[font="Courier New"]CREATE function dbo.BankRound (@val as numeric(30,15) )
returns money
as
begin
declare @factor int
set @factor = 100
declare @temp as double precision,
@fix_temp as double precision
set @temp = @val * @factor
set @fix_temp = floor(@temp + 0.5 * sign(@val))
if (@temp - cast(@temp as int) = 0.5)
begin
if ((@fix_temp / 2) <> cast(@fix_temp / 2 as int))
begin
set @fix_temp = @fix_temp - sign(@val)
end
end
return @fix_temp / @factor
end [/font]
-
Wow :w00t:
A cool headed amicable discussion about Banker's Rounding 😎
Amazing 😉
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply