Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Banker''s rounding in T-SQL (like Math.Round in .NET) Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, December 25, 2005 8:53 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21, Visits: 9
 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
Post #246556
 Posted Sunday, December 25, 2005 9:45 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:27 AM Points: 42,046, Visits: 39,430
 If you mean round to the nearest whole number, then simply converting to INT will do...DECLARE @Amount1 MONEY    SET @Amount1 = 7.49DECLARE @Amount2 MONEY    SET @Amount2 = 7.50DECLARE @Amount3 MONEY    SET @Amount3 = 7.51SELECT 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.01DECLARE @Amount2 MONEY    SET @Amount2 = 6.99DECLARE @Amount3 MONEY    SET @Amount3 = 7.00SELECT CAST(@Amount1/2 AS INT)*2SELECT CAST(@Amount2/2 AS INT)*2SELECT CAST(@Amount3/2 AS INT)*2...and it appears to work correctly with negative numbers, as well...DECLARE @Amount1 MONEY    SET @Amount1 = -6.01DECLARE @Amount2 MONEY    SET @Amount2 = -6.99DECLARE @Amount3 MONEY    SET @Amount3 = -7.00SELECT CAST(@Amount1/2 AS INT)*2SELECT CAST(@Amount2/2 AS INT)*2SELECT 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." Helpful Links:How to post code problemsHow to post performance problems
Post #246562
 Posted Monday, December 26, 2005 3:56 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21, Visits: 9
 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.45Math.Round(3.455,2) -> 3.46Math.Round(3.445,2) -> 3.44Math.Round(3.456,2) -> 3.46Pay 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.
Post #246575
 Posted Monday, December 26, 2005 9:34 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, November 24, 2016 7:36 AM Points: 2,316, Visits: 4,349
 This SQL uses the banker's rounding algorithm:DECLARE @Decimals smallintset @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 ) % 2then ROUND(BRAmt,@Decimals, 1 )ELSE ROUND(BRAmt,@Decimals, 0 )END as BRBankersRoundedAmtFROM (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
Post #246641
 Posted Monday, December 26, 2005 10:43 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21, Visits: 9
 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!
Post #246649
 Posted Tuesday, December 27, 2005 10:40 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, November 24, 2016 7:36 AM Points: 2,316, Visits: 4,349
 So the banker's rounding algorithm must be applied from right to left? Try this:create FUNCTION RoundBanker( @Amt numeric(38,16), @RoundToDecimal tinyint) RETURNS numeric(38,16)ASBEGINdeclare @RoundedAmt numeric(38,16), @WholeAmt integer, @Decimal tinyint, @Ten numeric(38,16)set @Ten = 10.0set @WholeAmt = ROUND(@Amt,0, 1 )set @RoundedAmt = @Amt - @WholeAmtset @Decimal = 16While @Decimal > @RoundToDecimalBEGIN 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 )ENDRETURN ( @RoundedAmt + @WholeAmt )ENDGO SQL = Scarcely Qualifies as a Language
Post #246763
 Posted Tuesday, December 27, 2005 10:48 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 3, 2009 7:34 AM Points: 21, Visits: 9
 Thank you Carl!!! That's what I need.
Post #246765
 Posted Tuesday, December 27, 2005 11:10 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:27 AM Points: 42,046, Visits: 39,430
 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.46DECLARE @Number MONEYDECLARE @Precision INT    SET @Precision = 2   SET @Number = 3.454SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)   SET @Number = 3.455SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)   SET @Number = 3.445SELECT ROUND(@Number,@Precision,CAST(@Number*POWER(10,@Precision) AS INT)%2)   SET @Number = 3.456SELECT 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." Helpful Links:How to post code problemsHow to post performance problems
Post #246775
 Posted Wednesday, December 28, 2005 6:28 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, May 29, 2012 11:22 AM Points: 1,755, Visits: 4,652
 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) ENDselect dbo.RoundBanker(3.454,2) --> 3.45select dbo.RoundBanker(3.455,2) --> 3.46select dbo.RoundBanker(3.445,2) --> 3.44select dbo.RoundBanker(3.456,2) --> 3.46select dbo.RoundBanker(3.445657545, 2) --> 3.45 Ryan RandallSolutions are easy. Understanding the problem, now, that's the hard part.
Post #246908
 Posted Wednesday, December 28, 2005 8:30 AM
 SSCrazy Group: General Forum Members Last Login: Thursday, November 24, 2016 7:36 AM Points: 2,316, Visits: 4,349
 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.453.4546 3.463.4653 3.463.4656 3.473.4643 3.463.4646 3.46The intermediate results from the looping solution are:First pair:Start with 3.4543Banker's Round at 3 gives 3.454Banker's Round at 2 gives 3.45Start with 3.4546Banker's Round at 3 gives 3.455Banker's Round at 2 gives 3.46Second Pair:Start with 3.4653Banker's Round at 3 gives 3.465Banker's Round at 2 gives 3.46Start with 3.4656Banker's Round at 3 gives 3.466Banker's Round at 2 gives 3.47 SQL = Scarcely Qualifies as a Language
Post #246934

 Permissions