SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Banker''s rounding in T-SQL (like Math.Round in .NET)


Banker''s rounding in T-SQL (like Math.Round in .NET)

Author
Message
Andrew Vasylevskyy
Andrew Vasylevskyy
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)

Group: General Forum Members
Points: 507665 Visits: 44277

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Andrew Vasylevskyy
Andrew Vasylevskyy
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 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.
Carl Federl
Carl Federl
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14728 Visits: 4355
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
Andrew Vasylevskyy
Andrew Vasylevskyy
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 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!
Carl Federl
Carl Federl
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14728 Visits: 4355
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
Andrew Vasylevskyy
Andrew Vasylevskyy
Mr or Mrs. 500
Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)Mr or Mrs. 500 (547 reputation)

Group: General Forum Members
Points: 547 Visits: 9
Thank you Carl!!! That's what I need.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)SSC Guru (507K reputation)

Group: General Forum Members
Points: 507665 Visits: 44277

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
RyanRandall
RyanRandall
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8357 Visits: 4652

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.
Carl Federl
Carl Federl
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14728 Visits: 4355
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search