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

  • Just realized the inner case statement was over complicated and I had a WHEN that could never be satisfied..

    revised:

    CREATE FUNCTION dbo.itvf_BankersRound(@Num decimal(38,9))

    RETURNS TABLE

    AS

    RETURN

    (

    SELECT

    CASE

    WHEN

    ROUND(@Num, 2) - @Num = .005

    AND ROUND(@Num, 2) % .02 <> 0

    THEN

    ROUND(@Num, 2) - 0.01

    ELSE ROUND(@Num, 2) END AS [Result]

    )

    GO

  • The default rounding method in .NET is Round-to-Even (or 'Bankers Rounding' as described in this thread). This type of rounding can be implemented in SQL Server quickly, easily and accurately using a CLR function that returns a value by simply using the .NET Decimal.Round method.

    I've tested such a CLR function against around 15 million values of datatype 'money' and the query completed in around 25s. I tried the first solution in this thread and I decided to stop the query after 10 minutes. In my situation the CLR solution wins the race.

    Chris

  • This was removed by the editor as SPAM

  • I'm amazed I still can't find an official or widely endorsed solution to this, but CodeMuddler's solution is the best I have found so far. CLR is not an option for us when using Azure SQL.

    Here is our version with some simplifications and assumptions on our max monetary amounts:

    • Round to 2 decimal places
    • Truncate input to decimal(10,4) (max value is ~1M or 999999.9999)
    • Return as decimal(8,2)  (max value is ~1M or 999999.99)

    Truncating to decimal(10,3) would probably also work, we just need the input to preserve enough decimal places for the rounding to work.

    CREATE FUNCTION dbo.fnBankersRound(@Num decimal(10,4))
    RETURNS decimal(8,2)
    AS
    BEGIN
    RETURN CASE
    WHEN ROUND(@Num, 2) - @Num = .005 AND ROUND(@Num, 2) % .02 <> 0
    THEN ROUND(@Num, 2) - 0.01
    ELSE ROUND(@Num, 2)
    END
    END

    Some tests to validate the rounding:

    declare @val decimal(10,4);
    set @val = 1.1149;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
    set @val = 1.1150;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
    set @val = 1.1249;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
    set @val = 1.1250;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
    set @val = 1.1349;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound
    set @val = 1.1350;
    select @val as Value, round(@val, 2) as Round, dbo.fnBankersRound(@val) as BankRound

    select
    100000000.01 / 800000000.01 as Value,
    round(100000000.01 / 800000000.01, 2) as Round,
    dbo.fnBankersRound(100000000.01 / 800000000.01) as BankRound

    And the results:

  • @GrassHopper's solution does not work for negative numbers, so here's his version that does work for negative numbers:

    create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
    begin
    return case
    when abs(round(@num, 2) - @num) = 0.005 and round(@num, 2) % 0.02 != 0
    then round(@num, 2) - 0.01 * sign(@num)
    else round(@num, 2)
    end
    end

    I also am amazed that it seems to have taken decades of attempts!

  • MarkCranness wrote:

    @Grasshopper's solution does not work for negative numbers, so here's his version that does work for negative numbers:

    create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
    begin
    return case
    when abs(round(@num, 2) - @num) = 0.005 and round(@num, 2) % 0.02 != 0
    then round(@num, 2) - 0.01 * sign(@num)
    else round(@num, 2)
    end
    end

    I also am amazed that it seems to have taken decades of attempts!

    It's ok.  We're amazed that it took you decades to get here and then wrote a scalar function instead of an iTVF.  😀

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I like this version better, because it does not have the magic constant "0.005" in it:

    create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
    begin
    return case
    when round(@num, 2, 1) % 0.02 = 0 then
    round(@num - round(@num, 0) - sign(@num), 2) + round(@num, 0) + sign(@num)
    else round(@num, 2)
    end
    end

    This works because round(d, n) is "round away from zero", and if we need to round (say) 1.125 towards even, that is round down/TOWARDS zero, then calculate round(1.125 - 2, 2) + 2 = round(-0.875, 2) + 2 = -0.88 + 2 = 1.12.

    Other dialects of SQL might instead use TRUNCATE(n, d) in place of ROUND(n, d, f):

    -- Non-MS SQL Server:
    create function dbo.RoundToEven(@num numeric(38,19)) returns decimal(19,2)
    begin
    return case
    when truncate(@num, 2) % 0.02 = 0 then
    round(@num - round(@num, 0) - sign(@num), 2) + round(@num, 0) + sign(@num)
    else round(@num, 2)
    end
    end

     

  • It's still a scalar function and your retry explains why this thread took so long and died.

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Today I found out what a SQL Inline Table Value Function is!

    I can't understand how an iTVF would be easier to use, I have a calculation like s0:

    select t.Key
    , dbo.RoundToEven(t.CreditBal - t.DebitBal)
    - isnull(
    sum(dbo.RoundToEven(e.Amount - e.Discount)
    + dbo.RoundToEven(dbo.RoundToEven(e.Amount - e.Discount) * TaxRate))
    , 0)
    from [Table] t
    left join TransactionEntries e on t.Key = e.Key
    group by t.Key, t.CreditBal, t.DebitBal

    In the database, the values are Float (not my design!), but I need numeric(19.2) results of that calculation.

    How would an iTVF work in this case?

    • This reply was modified 10 months, 1 week ago by  MarkCranness.
  • MarkCranness wrote:

    Today I found out what a SQL Inline Table Value Function is!

    I can't understand how an iTVF would be easier to use, I have a calculation like s0:

    select t.Key
    , dbo.RoundToEven(t.CreditBal - t.DebitBal)
    - isnull(
    sum(dbo.RoundToEven(e.Amount - e.Discount)
    + dbo.RoundToEven(dbo.RoundToEven(e.Amount - e.Discount) * TaxRate))
    , 0)
    from [Table] t
    left join TransactionEntries e on t.Key = e.Key
    group by t.Key, t.CreditBal, t.DebitBal

    In the database, the values are Float (not my design!), but I need numeric(19.2) results of that calculation.

    How would an iTVF work in this case?

    An iTVF is not necessarily "easier to use", nor is that its purpose.  It's purpose is to be nasty fast.

    Rather than taint this already tainted thread, I recommend you start a new thread for you new question.  In the meantime, have a gander at the following article.  It may explain how to incorporate your code into an iTVF but it will certainly explain why to use them instead of Scalar functions.  It's a very old but still appropriate article even today.

    https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

     

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

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 46 through 54 (of 54 total)

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