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»»»

Banker''s rounding in T-SQL (like Math.Round in .NET) Expand / Collapse
Author
Message
Posted Sunday, December 25, 2005 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437

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

(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 #246562
Posted Monday, December 26, 2005 3:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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.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.
Post #246575
Posted Monday, December 26, 2005 9:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:58 AM
Points: 2,281, Visits: 4,241
This SQL uses the banker's rounding algorithm:

DECLARE @Decimals smallint
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
Post #246641
Posted Monday, December 26, 2005 10:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:58 AM
Points: 2,281, Visits: 4,241
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)
AS
BEGIN
declare @RoundedAmt numeric(38,16)
, @WholeAmt integer
, @Decimal tinyint
, @Ten numeric(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
Post #246763
Posted Tuesday, December 27, 2005 10:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437

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

(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 #246775
Posted Wednesday, December 28, 2005 6:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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)

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.
Post #246908
Posted Wednesday, December 28, 2005 8:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 9:58 AM
Points: 2,281, Visits: 4,241
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
Post #246934
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse