Banker''s rounding in T-SQL (like Math.Round in .NET)
-
Hi!
I'm in extreme need of T-SQL function wich will round to the nearest even number, called banker's rounding or rounding to nearest (like Math.Round in .NET)
There are lots of discussions about rounding and realizations of banker's rounding in C#,VB,C++ but I haven't found any realization in SQL. Using exdended procedure is a solution but it's not to my liking.
I really can't think out it by myself.
Thanks in advance
-
If you mean round to the nearest whole number, then simply converting to INT will do...
DECLARE @Amount1 MONEY
SET @Amount1 = 7.49
DECLARE @Amount2 MONEY
SET @Amount2 = 7.50
DECLARE @Amount3 MONEY
SET @Amount3 = 7.51
SELECT CAST(@Amount1 AS INT)
SELECT CAST(@Amount2 AS INT)
SELECT CAST(@Amount3 AS INT)
If you really mean to round to the nearest EVEN number (as you say, Banker's rounding), that is, the nearest number evenly divisible by 2, then this will do (the forumula)...
DECLARE @Amount1 MONEY
SET @Amount1 = 6.01
DECLARE @Amount2 MONEY
SET @Amount2 = 6.99
DECLARE @Amount3 MONEY
SET @Amount3 = 7.00
SELECT CAST(@Amount1/2 AS INT)*2
SELECT CAST(@Amount2/2 AS INT)*2
SELECT CAST(@Amount3/2 AS INT)*2
...and it appears to work correctly with negative numbers, as well...
DECLARE @Amount1 MONEY
SET @Amount1 = -6.01
DECLARE @Amount2 MONEY
SET @Amount2 = -6.99
DECLARE @Amount3 MONEY
SET @Amount3 = -7.00
SELECT CAST(@Amount1/2 AS INT)*2
SELECT CAST(@Amount2/2 AS INT)*2
SELECT CAST(@Amount3/2 AS INT)*2
--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) -
Thanks Jeff. But none of them is banker's rounding.
Banker's rounding bahaves like arithmetic rounding with one exception: if value is halfway between two numbers, one of which is even and the other odd, then the even number is returned.
Examples of banker's rounding (results of Math.Round in .NET):
Math.Round(3.454,2) -> 3.45
Math.Round(3.455,2) -> 3.46
Math.Round(3.445,2) -> 3.44
Math.Round(3.456,2) -> 3.46
Pay your attention on lines 2 and 3. Result depends on wether the second digit after dot is even or odd.
Complexity is that I perform calcualtions with double and finaly need to round the result. So values can have lots of digits after dot and I have to round them one by one to the precision digit.
-
This SQL uses the banker's rounding algorithm:
DECLARE@Decimalssmallint
set@Decimals= 2
select BRAmt
,BRExpectedAmt
, CASE
WHEN 5 = ( ROUND(BRAmt * POWER( 10, @Decimals + 1 ) ,0,1) - (ROUND(BRAmt * POWER( 10, @Decimals ) ,0,1) * 10) )
AND 0 = cast( ( ROUND(BRAmt * POWER( 10, @Decimals ) ,0,1) - (ROUND(BRAmt * POWER( 10, @Decimals - 1 ) ,0,1) * 10) ) AS INTEGER ) % 2
then ROUND(BRAmt,@Decimals, 1 )
ELSE ROUND(BRAmt,@Decimals, 0 )
END as BRBankersRoundedAmt
FROM(select cast ( 3.454 as numeric(8,4) ) , cast(3.45 as numeric(6,2) ) union all
select cast ( 3.455 as numeric(8,4) ) , cast(3.46 as numeric(6,2) )union all
select cast ( 3.445 as numeric(8,4) ) , cast(3.44 as numeric(6,2) )union all
select cast ( 3.456 as numeric(8,4) ) , cast(3.46 as numeric(6,2) )
) as BR (BRAmt, BRExpectedAmt)
SQL = Scarcely Qualifies as a Language
-
That's realy baker's rounding but with one weakness - it rounds only one digit before precision digit.
For instance: rounding of 3.445657545 to second digit after dot takes no account of digits on the right.
It should round digit by digit for the rightest to the precision digit. That's the main problem!
-
So the banker's rounding algorithm must be applied from right to left? Try this:
create FUNCTION RoundBanker
( @Amtnumeric(38,16)
, @RoundToDecimal tinyint
)
RETURNS numeric(38,16)
AS
BEGIN
declare@RoundedAmtnumeric(38,16)
,@WholeAmtinteger
,@Decimaltinyint
,@Tennumeric(38,16)
set@Ten= 10.0
set@WholeAmt= ROUND(@Amt,0, 1 )
set@RoundedAmt= @Amt - @WholeAmt
set@Decimal= 16
While @Decimal > @RoundToDecimal
BEGIN
set @Decimal = @Decimal - 1
if 5 = ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal + 1 ) ,0,1) - (ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) * 10) )
and 0 = cast( ( ROUND(@RoundedAmt * POWER( @Ten, @Decimal ) ,0,1) - (ROUND(@RoundedAmt * POWER( @Ten, @Decimal - 1 ) ,0,1) * 10) ) AS INTEGER ) % 2
SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 1 )
ELSE
SET @RoundedAmt = ROUND(@RoundedAmt,@Decimal, 0 )
END
RETURN ( @RoundedAmt + @WholeAmt )
END
GO
SQL = Scarcely Qualifies as a Language
-
Thank you Carl!!! That's what I need.
-
Andrew... This also works... and, it's set based.... just substitute a column name for @Number in the formula...
-- Math.Round(3.454,2) -> 3.45
-- Math.Round(3.455,2) -> 3.46
-- Math.Round(3.445,2) -> 3.44
-- Math.Round(3.456,2) -> 3.46
DECLARE @Number MONEY
DECLARE @precision INT
SET @precision = 2
SET @Number = 3.454
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.455
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.445
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.456
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
--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) -
Jeff - I think that may not work for the 3.445657545 example (which should go to 3.45).
Here's an alternative (non-looping) solution:
CREATE FUNCTION dbo.RoundBanker (@x money, @DecimalPlaces tinyint)
RETURNS money AS
BEGIN
set @x = @x * power(10, @DecimalPlaces)
return
case when @x = floor(@x) then @x
else
case sign(ceiling(@x) - 2*@x + floor(@x))
when 1 then floor(@x)
when -1 then ceiling(@x)
else 2*round(@x/2,0) end
end / power(10, @DecimalPlaces)
END
select dbo.RoundBanker(3.454,2) --> 3.45
select dbo.RoundBanker(3.455,2) --> 3.46
select dbo.RoundBanker(3.445,2) --> 3.44
select dbo.RoundBanker(3.456,2) --> 3.46
select dbo.RoundBanker(3.445657545, 2) --> 3.45
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Jeff and Ryan, try your solutions with these four test cases.
The differences between the pairs is in the second decimal position number being odd or even.
The difference between a pairs of numbers is in the fourth decimal position where the first is less than 5 and the second is greater than 5.
InAmt ExpectedAmt
------- -----------
3.4543 3.45
3.4546 3.46
3.4653 3.46
3.4656 3.47
3.4643 3.46
3.4646 3.46
The intermediate results from the looping solution are:
First pair:
Start with 3.4543
Banker's Round at 3 gives 3.454
Banker's Round at 2 gives 3.45
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
Start with 3.4656
Banker's Round at 3 gives 3.466
Banker's Round at 2 gives 3.47
SQL = Scarcely Qualifies as a Language
-
Ryan, You are correct. It doesn't work correctly for 3.445657545.
It rounds to 3.44 instead of 3.45 as you stated. Thanks for the catch and I apologize to everyone for not testing a bit deeper. 
Back to the drawing board! 
--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) -
Ryan,
Your code works great so long as the MONEY data type is used to hold the number and is probably appropriate for the Banker's Rounding problem because one must assume that bankers will round to the penny.
However, if the data type to hold the number is converted to the DECIMAL data type (to allow for rounding at, say, the 5th decimal place) and using the very same test number you gave me, it comes up with the same incorrect answer as mine did... here's the code I used...
--drop function dbo.RoundBanker
CREATE FUNCTION dbo.RoundBanker (@x decimal(38,20), @DecimalPlaces tinyint)
RETURNS money AS
BEGIN
set @x = @x * power(10, @DecimalPlaces)
return
case when @x = floor(@x) then @x
else
case sign(ceiling(@x) - 2*@x + floor(@x))
when 1 then floor(@x)
when -1 then ceiling(@x)
else 2*round(@x/2,0) end
end / power(10, @DecimalPlaces)
END
GO
SELECT dbo.RoundBanker (3.445657545 , 2) --<-- results in 3.44 instead of 3.45 as it should.
What an interesting problem...
Carl, I haven't explored your looping answer because I'm interested in accomplishing this "simple"
math problem without a loop of any kind... say, weren't you the one the recently tried to prove the hypothesis that UDF's should never be allowed in code save the one that Adam Mechanic wrote? 
What's up with that? 
--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) -
AND, dont' feel too alone on that MONEY v.s. DECIMAL thing... I just changed to the DECIMAL data type on my attempted code and ALL the answers became 3.45
some of which are obviously incorrect ... here's the code in case anybody else wants to "play"...--Example numbers and the correct desired answers
-- Math.Round(3.445657545,2) -> 3.45 --<-- Ryan's test number
-- Math.Round(3.454,2) -> 3.45
-- Math.Round(3.455,2) -> 3.46
-- Math.Round(3.445,2) -> 3.44
-- Math.Round(3.456,2) -> 3.46
DECLARE @Number DECIMAL(38,20)
DECLARE @precision INT
SET @precision = 2
SET @Number = 3.445657545
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.454
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.455
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.445
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
SET @Number = 3.456
SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)
--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) -
BWAAA-HAAAA-HAAAA-HAAA! Snooorrtt! OOOOOOOHHHHH-WEEEEEE! UNBELIEVABLE!!!! WE DON' NEED NO STINKIN' LOOPS... WE DON' NEED NO STINKIN' UDF. WE DON' NEED NO STINKIN' CALCULATIONS!!!! SQL SERVER HAS A FUNCTION TO DO BANKERS ROUNDING AUTO-MAGICALLY!!!! I JUST CAN'T QUIT LAUGHING AT ALL THE STUFF WE WENT THROUGH AND IT'S RIGHT UNDER OUR NOSES!!! GOTTA LOVE THOSE UNDOCUMENTED FEATURES!!!!--===== 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) --Bankers Rounding expected
SELECT 3.456 UNION ALL --<3.46
SELECT 3.455 UNION ALL --<3.46
SELECT 3.454 UNION ALL --<3.45
SELECT 3.445657545 UNION ALL --<3.45
SELECT 3.445 UNION ALL --<3.44
SELECT 3.435 UNION ALL --<3.44
SELECT 3.425 UNION ALL --<3.42
SELECT 3.415 UNION ALL --<3.42
SELECT 3.405 UNION ALL --<3.40
SELECT 3.395 --<3.40
--===== Demonstrate the "extremely complicated" solutionSELECT STR(Number,10,2)
FROM #MyTemp
HAAAA-HAAAA-HAAAA!!!! OH LORDY! BILL GATES STRIKES AGAIN!!! THIS IS TOO FUNNY!!!Hey! Anybody with SQL Server 7 or 2005... please try the code above and see if you get the same answers and let us know, please! Thanks, alot!!! (Hee-hee-hee, I just can't stop laughing about this).And now, my laughter turns to crys of sorrow because I just realized how many places me and my guys have used this damned function thinking that it rounded in a traditional sense. Man! I've gotta lotta code to fix!!!!{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) -
Jeff, STR seems to round only last digit before precision. It doesn't take into account digits to the rigth.
Try this number: 3.4546
banker's round -> 3.46
STR -> 3.45
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply