October 9, 2007 at 7:33 am
Heh... flies haven't found it, yet... :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2007 at 9:56 am
Here's my version of Banker's Rounding.
I tried to make it similar to existing ROUND, which can do SELECT ROUND(250, -2). Arithmetic Rounding rounds 250 to 300.
SELECT dbo.f_BankersRound(250, -2) yields 200.000000...
This implementation works for input up to 16th decimal, and is not hardcoded to money type.
Please let me know if you find any bug with it.
(I found a bug, and reposted on 10/10)
-- =============================================
-- Author Yu Yokota
-- =============================================
CREATE FUNCTION [dbo].[f_BankersRound](
@aInput DECIMAL(38, 16),
@aDecimalPlace INT)
RETURNS DECIMAL(38, 16)
AS
BEGIN
DECLARE @isNegative BIT;
DECLARE @inputAbs DECIMAL(38, 16);
DECLARE @leftPart DECIMAL(38, 16);
DECLARE @rightPart DECIMAL(38, 16);
DECLARE @halfWay DECIMAL(38, 16);
DECLARE @ten DECIMAL(38, 16);
-- Separate the input into @isNegative and @inputAbs
IF @aInput < 0
BEGIN
SET @isNegative = 1;
END ELSE
BEGIN
SET @isNegative = 0;
END
SET @inputAbs = ABS(@aInput);
-- Truncate the aInput and store it as @leftPart
SET @leftPart = ROUND(@inputAbs, @aDecimalPlace, 1);
-- Store the part to be rounded as @rightPart
SET @rightPart = @inputAbs - @leftPart;
-- Calculate the halfway point for rounding
SET @ten = 10;
SET @halfWay = POWER(@ten, -@aDecimalPlace) * 0.5;
-- If the @rightPart is not exactly half way,
-- the result is the same as the Arithmetic Rounding
IF @rightPart <> @halfWay
BEGIN
RETURN ROUND(@aInput, @aDecimalPlace, 0)
END -- IF
-- If the last digit of the @leftPart is odd,
-- the result is the same as the Arithmetic Rounding
IF (@leftPart * 0.5)
<> ROUND(@leftPart * 0.5, @aDecimalPlace, 1)
BEGIN
RETURN ROUND(@aInput, @aDecimalPlace, 0)
END
-- If the last digit is even, Truncate
IF @isNegative = 1
BEGIN
RETURN -@leftPart
END
RETURN @leftPart
END
go
October 9, 2007 at 10:18 am
Anyone check out the other threads that we had on this topic? I thought we had a solution to this somewhere in there with all the mud flying around.
October 9, 2007 at 10:29 am
This is the function I had posted back during the war:
CREATE FUNCTION [dbo].[fn_BRound] (
@val decimal(38,20),
@pos int
)
RETURNS decimal(38,20)
as begin
declare @tmpval1 int,
@tmpval2 decimal(38,20),
@retval decimal(38,20),
@tmpval3 decimal(38,20),
@tmpval4 decimal(38,20)
set @tmpval1 = floor(abs(@val) * power(cast(10 as float), @pos))
set @tmpval2 = round(@val, @pos, 1)
set @tmpval3 = sign(@val) * (0.5 * power(cast(10 as float), (-1 * @pos)))
set @tmpval4 = (@val - @tmpval2)
set @retval = round(@val, @pos, case
when nullif(@tmpval1, (@tmpval1 / 2) * 2) is null
and ((@tmpval3 >= @tmpval4 and sign(@val) = 1)
or (@tmpval4 >= @tmpval3 and sign(@val) = -1))
then 1
else 0 end)
return @retval
end
March 1, 2008 at 8:52 am
using select dbo.fn_BRound(5.344515,2) gives you 5.34 and not 5.35. Any clues on how I can get 5.35 from this number?:crying:
March 1, 2008 at 1:08 pm
pbushong (3/1/2008)
using select dbo.fn_BRound(5.344515,2) gives you 5.34 and not 5.35. Any clues on how I can get 5.35 from this number?:crying:
You won't. You are rounding to the 100ths decimal place, even standard rounding will return 5.34 not 5.35. The reason is the value in the 1000th's place is a 4 (5.344), and this causes the value to round down to 5.34.
😎
March 1, 2008 at 1:49 pm
Thank you for your feedback. I was trying to find a function in sql that would give me the same answer like roundup does in excel.
Example: If I use round(5.344515,2) in excel I get 5.34
and if I use roundup(5.344515,2) in excel I get 5.35.:crazy:
April 3, 2009 at 7:02 am
A clarification on Banker's rounding for everyone - 3.4546 rounded to two decimals IS NOT 3.46. It should be 3.45 - you do not work backwards and round from right to left. You only need the next digit. The idea behind banker's rounding is that you will eventually end up with even amounts when splitting pennies - therefore the whole theory in the Office Space millions is blown out of the water unless they weren't actually rounding.
So I've found Ryan's function to work perfectly.
June 27, 2011 at 9:53 am
Just wanted to say that I've been attempting to find a quick way to implement half even rounding in SQL, because I wanted to replicate the numbers generated by Java. Lynn's function worked perfectly, so many thanks for that. Ryan's implementation higher up the chain did not work as expected when rounding 2.507186 to 5 decimal places. As you might have guessed from the fact that I spent several hours trying to find someone else's implementation rather than making my own, I don't have the knowledge to explain why Ryan's function rounds down...
I actually spent most of the day reading over the several threads on this topic. It made for some interesting reading, but that had more to do with the blatant trolling than anything else.
June 27, 2011 at 11:50 am
I am glad that this old thread was of use to you, and I am glad my routine was useful as well.
Thank you for feedback.
January 13, 2012 at 8:37 am
Here is another solution. This technique can be used in a function or directly inline within a select as shown here.
select dec, value,
round(value+((mod(trunc(power(10,dec)*value),2)-sign(value))*power(10,-length(value-trunc(value)))),dec) "Banker's Round"
from
(
select 2 dec, 0 value from dual union all
select -2, 1650 from dual union all
select -2, 1750 from dual union all
select -3, 23500 from dual union all
select -3, 24500 from dual union all
select -2, -1650 from dual union all
select -2, -1750 from dual union all
select -3, -23500 from dual union all
select -3, -24500 from dual union all
select 2, 7.345 from dual union all
select 2, 3.454 from dual union all
select 2, 3.455 from dual union all
select 2, 3.445 from dual union all
select 2, 3.456 from dual union all
select 2, 3.4543 from dual union all
select 2, 3.4546 from dual union all
select 2, 3.4653 from dual union all
select 2, 3.4656 from dual union all
select 2, 3.4643 from dual union all
select 2, 3.4646 from dual union all
select 8, 3.445657545 from dual union all
select 2, 3.445 from dual union all
select 2, 3.435 from dual union all
select 2, 3.425 from dual union all
select 2, 3.415 from dual union all
select 2, 3.405 from dual union all
select 2, 3.395 from dual union all
select 2, -7.345 value from dual union all
select 2, -3.454 from dual union all
select 2, -3.455 from dual union all
select 2, -3.445 from dual union all
select 2, -3.456 from dual union all
select 2, -3.4543 from dual union all
select 2, -3.4546 from dual union all
select 2, -3.4653 from dual union all
select 2, -3.4656 from dual union all
select 2, -3.4643 from dual union all
select 2, -3.4646 from dual union all
select 8, -3.445657545 from dual union all
select 2, -3.445 from dual union all
select 2, -3.435 from dual union all
select 2, -3.425 from dual union all
select 2, -3.415 from dual union all
select 2, -3.405 from dual union all
select 2, -3.395 from dual
)
April 10, 2012 at 11:59 am
The speed is poblem !!
This funkction is about 10 times slower then standard round()
April 10, 2012 at 12:37 pm
softech (4/10/2012)
The speed is poblem !!This funkction is about 10 times slower then standard round()
That is one of the other factors for this: a different solution is required to do bankers Rounding vs Regular rounding, so Regular Rounding cannot be used.
I don't think anyone has optimized the function examples provided so far, so it will not perform as well as a built in, native function.
Lowell
April 10, 2012 at 2:20 pm
If anyone is interested, I just rewrote my scalar function as an inline table valued function:
create function dbo.itvf_BRound(
@val decimal(38,20),
@pos int
) returns table with schemabinding
as return(
with BaseComp1 as (
select
cast(floor(abs(@val) * power(cast(10 as float), @pos)) as int) tmpval1,
cast(round(@val, @pos, 1) as decimal(38,20)) tmpval2,
cast(sign(@val) * (0.5 * power(cast(10 as float), (-1 * @pos))) as decimal(38,20)) tmpval3
),
BaseComp2 as (
select
cast((@val - tmpval2) as decimal(38,20)) tmpval4
from
BaseComp1)
select
cast(round(@val, @pos, case
when nullif(tmpval1, (tmpval1 / 2) * 2) is null
and ((tmpval3 >= tmpval4 and sign(@val) = 1)
or (tmpval4 >= tmpval3 and sign(@val) = -1))
then 1
else 0 end) as decimal(38,20)) val
from
BaseComp1 cross join BaseComp2
);
April 12, 2012 at 5:26 pm
Here's what I came up with. Granted I made a pretty sweeping assumption (always rounding to hundredths), but that was the business case I was working with. It would be easy enough to add a precision parameter and replace some of the hardcoded #s with the calculations used to derive them, but for the typical use (actual bankers rounding to the nearest cent) this seems to work.
CREATE FUNCTION dbo.itvf_BankersRound(@Num decimal(38,9))
RETURNS TABLE
AS
RETURN
(
SELECT
CASE
WHEN ROUND(@Num, 2) - @Num = .005
THEN
ROUND(@Num, 2) + CASE
WHEN
ROUND(@Num, 2) % .02 <> 0
AND ROUND(@Num, 2) > @Num
THEN -0.01
WHEN
ROUND(@Num, 2) % .02 <> 0
AND ROUND(@Num, 2) < @Num
THEN .01
ELSE 0
END
ELSE ROUND(@Num, 2) END AS [Result]
)
GO
blog: http://www.codemuddler.com
twitter: http://www.twitter.com/CodeMuddler
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply