Round to Even (aka Banker”s Rounding) – The final function

  • Suresh Rao

    SSC Rookie

    Points: 28

    I have not found any function in SQL Server 2005 to peform bankers rounding.

    You do have one in the Dot Net Framework

    Math.Round Method (Decimal, MidpointRounding)
     
    If you are using SSRS you can use framework functions in the Report Designer.
     
    One work around that worked for me is to increase the precision to 3-4 digits more than required ( I needed 6 , the i used 10 digits ) and then round the result. This does not work if the summation is for thousands of rows. 
  • Bob Bobbity

    SSC Veteran

    Points: 210

    Two questions please, having ploughed my way through this lesson in trolling:

    1. Was there a final version of the banker’s rounding algorithm which rounded correctly to 5 decimal places any “representation of a number” (to avoid concerns over imprecise representations of numbers) stored in a decimal(38,12) field? I have a real-world need, where one of our systems connects with another which is doing this rounding, and the SQL database is not.

    I have an algorithm already (based I believe on one of those in this thread) which uses the power() function, and works fine except that this breaks down once the data being rounded is greater than 92,233,720,368,547.8 (rounded up from .746something), which is obviously less than the maximum possible in the decimal field. The example that I am failing on is 99999999999999.123565 to 5 decimal places, which should of course be 99999999999999.12356

    2. We have clients based in New Zealand. Will they be arrested?

  • Bob Bobbity

    SSC Veteran

    Points: 210

    Urrgh

    I have just identified the issue as being with my function, and one that I corrected elsewhere some time ago. Please ignore.

    For reference, below is the function being used, which handles the two cases posted on the first page of this thread as well as our regular requirement (rounding to 5 places)

    CREATE FUNCTION dbo.RoundBanker (@val decimal(38,12), @pos int)

    RETURNS decimal(38,12)

    as

    — Takes two parameters. First is number to be rounded, second is how many places to round to

    ———————————————————————————————

    begin

    declare

    @tmpval1 bigint,

    @tmpval2 decimal(38,12),

    @retval decimal(38,12),

    @tmpval3 decimal(38,12),

    @tmpval4 decimal(38,12),

    @predec decimal(38,12),

    @postdec decimal(38,12)

    — To get most from function, ignore everything before decimal point

    ——————————————————————–

    set @predec = floor(abs(@val))

    set @postdec = case sign(@val) when 1 then @val – @predec else @val + @predec end

    — Actual work (Lynn’s)

    ———————-

    set @tmpval1 = floor(abs(@postdec) * power(cast(10 as float), @pos))

    set @tmpval2 = round(@postdec, @pos, 1)

    set @tmpval3 = sign(@postdec) * (0.5 * power(cast(10 as float), (-1 * @pos)))

    set @tmpval4 = (@postdec – @tmpval2)

    set @retval = round(@postdec, @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)

    — Rebuild number

    —————–

    select @retval = case sign(@val) when 1 then @retval + @predec else 0.0 – @predec + @retval end

    return @retval

    end

Viewing 3 posts - 376 through 378 (of 378 total)

You must be logged in to reply to this topic. Login to reply