Round to Even (aka Banker''s Rounding) - The final function

  • David McFarland

    SSChampion

    Points: 11815

    Since the other thread was trainwrecked, but since there is indeed a problem with the function as currently written, I thought we should start a new thread to fix the problem. This is a valuable tool for many toolboxes, and agreeing on one that meets the specification would be a boon to those who need it. If you think banker's rounding is stupid, we have a thread for you to voice your opinion here. Please keep it out of this one.

    To start, I'd like to ask David Jackson to post the latest iteration of the function, so we can go from there. My initial impression is that we might want to end up with two versions, one handling decimal/numeric data as input, and another with float as input, to help reduce the issues that arise from floating point numbers on computers.

    David?

  • David Jackson

    SSCertifiable

    Points: 6404

    Certainly.  This uses Double Precision, which I've never heard of in T-SQL until recently, but it still has trouble with certain values. 

    Although it does pass the following, which Lynns doesn't until you change the decimal data type to Double Precision. 

    select 100000000.01 / 800000000.01, 

           dbo.fn_Bround(100000000.01 / 800000000.01,100), --mine

           dbo.fn_Bround2(100000000.01 / 800000000.01,2),  --Lynns

           Round(100000000.01 / 800000000.01,2)

    select cast(1 as float) / cast(8 as float),

           dbo.fn_Bround(cast(1 as float) / cast(8 as float),100), 

           Round(cast(1 as float) / cast(8 as float),2)

    I've not bothered with 2/3 as we both know what that will do.

    ALTER FUNCTION FN_BROUND

                  (@p1 DOUBLE PRECISION,

                   @p2 INT)

    RETURNS MONEY

    /*

    Round-to-even method (from http://en.wikipedia.org/wiki/Rounding)

    This method is also known as unbiased rounding or as statistician's rounding or as bankers' rounding.

    It is identical to the common method of rounding except when the digit(s) following the rounding digit

    start with a five and have no non-zero digits after it. The new algorithm is:

        * Decide which is the last digit to keep.

        * Increase it by 1 if the next digit is 6 or more, or a 5 followed by one or more non-zero digits.

        * Leave it the same if the next digit is 4 or less

        * Otherwise, all that follows the last digit is a 5 and possibly trailing zeroes;

          then change the last digit to the nearest even digit. That is, increase the rounded

          digit if it is currently odd; leave it if it is already even.

    With all rounding schemes there are two possible outcomes: increasing the rounding digit by one or

    leaving it alone. With traditional rounding, if the number has a value less than the half-way mark

    between the possible outcomes, it is rounded down; if the number has a value exactly half-way or

    greater than half-way between the possible outcomes, it is rounded up. The round-to-even method is the

    same except that numbers exactly half-way between the possible outcomes are sometimes rounded up—sometimes down.

    Although it is customary to round the number 4.5 up to 5, in fact 4.5 is no nearer to 5 than it is to 4

    (it is 0.5 away from either). When dealing with large sets of scientific or statistical data, where trends

    are important, traditional rounding on average biases the data upwards slightly. Over a large set of

    data, or when many subsequent rounding operations are performed as in digital signal processing, the

    round-to-even rule tends to reduce the total rounding error, with (on average) an equal portion of numbers

    rounding up as rounding down. This generally reduces the upwards skewing of the result.

    Round-to-even is used rather than round-to-odd as the latter rule would prevent rounding to a result of zero.

    Examples:

        * 3.016 rounded to hundredths is 3.02 (because the next digit (6) is 6 or more)

        * 3.013 rounded to hundredths is 3.01 (because the next digit (3) is 4 or less)

        * 3.015 rounded to hundredths is 3.02 (because the next digit is 5, and the hundredths digit (1) is odd)

        * 3.045 rounded to hundredths is 3.04 (because the next digit is 5, and the hundredths digit (4) is even)

        * 3.04501 rounded to hundredths is 3.05 (because the next digit is 5, but it is followed by non-zero digits)

    -- =============================================

    -- Example to execute function

    -- =============================================

    SELECT dbo.fn_BRound (282.26545, 100) -- 100 to nearest penny, 20 to nearest 5 pence, 10 to nearest 10 pence,

                                          -- 5 to nearest 20 pence, 2 to nearest 50 pence, 1 to nearest pound/dollar/euro

            Code (poorly?) converted from VB example @ http://support.microsoft.com/kb/196652

    */

    AS

      BEGIN

        DECLARE  @Temp    DOUBLE PRECISION,

                 @FixTemp DOUBLE PRECISION

                          

        SELECT @Temp = @p1 * @p2

        

        --This is the closest I can get, but it is still wrong for certain values

        SELECT @FixTemp = SIGN(@Temp + 0.5 * SIGN(@p1)) * FLOOR(ABS(@Temp + 0.5 * SIGN(@p1)))

                                                          

        -- Handle rounding of .5 in a special manner

        IF @Temp - FLOOR(@Temp) = 0.5

          BEGIN

            IF @FixTemp / 2 <> FLOOR(@FixTemp / 2)  -- Is Temp odd

              -- Reduce Magnitude by 1 to make even

              SELECT @FixTemp = @FixTemp - SIGN(@p1)

          END

          

        RETURN @FixTemp / @p2

      END

    GO

    SET QUOTED_IDENTIFIER  OFF

    GO

    SET ANSI_NULLS  ON

    GO

    /* Lynns Function is here as a sense check */

    -----------------------------------------------------------------------------------------------

    ALTER FUNCTION dbo.fn_Bround2

                  (@TestValue DECIMAL(38,12),

                   @pos       INT)

    RETURNS MONEY

    AS

      BEGIN

        RETURN ROUND(@TestValue,@pos,CASE

                                       WHEN (NULLIF(ROUND(@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos),

                                                          0,1),ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) / 2,

                                                                     0,1) * 2) IS NOT NULL)

                                             OR (NULLIF(ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)),

                                                              0,1),ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) / 2,

                                                                         0,1) * 2) IS NULL

                                                 AND (@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)) - ROUND((@TestValue * POWER(CAST(10 AS DECIMAL(38,12)),@pos)),

                                                                                                                   0,1) > .5) THEN 0

                                       ELSE 1

                                     END)

      END

    GO

    HTH

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • noeld

    SSC Guru

    Points: 96590

    >>This uses Double Precision, which I've never heard of in T-SQL until recently, but it still has trouble with certain values. <<

    From BOL definition of float:

    The synonym for *double precision* is *float(53)*.


    * Noel

  • Sergiy

    SSC Guru

    Points: 109668

    Whole bunch of examples where FLOAT (DOUBLE PRECISION) fails is in 1st topic.

    Regarding second one:

    DECLARE

    @enumerator DECIMAL(38, 12)

    ,@denominator DECIMAL(38, 12)

    SELECT

    @enumerator = 50

    ,@denominator = 111.111

    SELECT @enumerator/@denominator,

    dbo.fn_BRound2(@enumerator/@denominator, 100)

    ,Round(@enumerator/@denominator,2)

    Server: Msg 8115, Level 16, State 6, Procedure fn_Bround2, Line 7

    Arithmetic overflow error converting float to data type numeric.

    Try harder.

  • David McFarland

    SSChampion

    Points: 11815

    I'll try and summarize the Wiki entry that is in the code comments, so that everyone understands what it is supposed to do.

    Banker's rounding is identical to traditional rounding with a single exception. When the digit to the immediate right of the desired final display digit is a 5, and when no non-zero characters follow it, you round to the nearest even digit. That's it.

    Examples:

    When rounding to 2 decimal places...

    0.1249999999999999999999999999999999999999999 returns 0.12 using both methods

    0.1250000000000000000000000000000000000000001 returns 0.13 using both methods

    0.1250000000000000000000000000000000000000000 rounds down to .12 using Banker's rounding, since the second digit, 2, is an even number, and up to .13 using traditional rounding, since traditional rounding always rounds up at 5 or greater.

    On the other hand....

    0.1349999999999999999999999999999999999999999 returns 0.13 using both methods

    0.1350000000000000000000000000000000000000001 returns 0.14 using both methods

    0.1350000000000000000000000000000000000000000 rounds up to 0.14 using Banker's rounding, since the second digit, 3, is odd, and the nearest even number is 4 (0.14 is much closer than 0.12 to 0.135), while traditional rounding also returns 0.14, since it always rounds up at 5 or greater.

    As you can see, they behave differently very rarely, but when they do, it's intentional, so that's what we need to provide for.

     

  • Sergiy

    SSC Guru

    Points: 109668

    Here is the need to provide for:

    select dbo.fn_BRound(10./7998*1000, 100)

  • David McFarland

    SSChampion

    Points: 11815

    While this is a trick on his part, I actually agree with him, even though he'll disagree with what this function should do with it (he can take that to the other thread). What he's not telling you is that SQL Server will do an implicit round on this number before it ever gets to the function, so the function will actually only receive .125 as a parameter, instead of its actual value which is something like 1.2503125781445361340335083770943e-6. We should indeed be able to handle that, and based on the rules above (which is what this thread is about), should return .12.

    On the other hand, if the function actually receives the value 1.2503125781445361340335083770943e-6 from SQL Server, then we should return .13, based on the rules above.

    So yes, he's correct, we should be able to handle that. It's just that we'll handle it differently depending on whether we get the implicitly rounded version or the more accurate version of the number, as that's how Banker's rounding is designed, again, as noted above.

  • David Jackson

    SSCertifiable

    Points: 6404

    Sergiy

    you was politely asked to stay out of this thread. The reason you got an error was because you are using Lynns function, which does not take the same parameters as mine. Try:

    DECLARE

    @enumerator DECIMAL(38, 12)

    ,@denominator DECIMAL(38, 12)

    SELECT

    @enumerator = 50

    ,@denominator = 111.111

    SELECT @enumerator/@denominator,

    dbo.fn_BRound2(@enumerator/@denominator, 2)

    ,Round(@enumerator/@denominator,2)

    Note: I'm at home now, and can't test this, (Not got SQL at Home), but simple proof reading, even after a beer, tells me you are not bothering to even read the thread properly.

    Read Harder.

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • David McFarland

    SSChampion

    Points: 11815

    David, while I agree that it was his intention, he didn't actually crap in this thread (I realize that the day isn't over yet), as his post was useful to demonstrate what the function should be expected to do. He might not like the answer as to how it should behave in each of the two given situations, but that is what Banker's rounding is supposed to do.

  • David Jackson

    SSCertifiable

    Points: 6404

    I can't test this now, but his "Try harder." comment, when the error was on his part on how he called the function, got to me . Sorry.  

    But of course he won't admit it

    Dave J


    http://glossopian.co.uk/
    "I don't know what I don't know."

  • Sergiy

    SSC Guru

    Points: 109668

    Sorry, there was no comment about change in using of parameter.

    I tried:

    DECLARE

    @enumerator DECIMAL(38, 12)

    ,@denominator DECIMAL(38, 12)

    SELECT

    @enumerator = 5

    ,@denominator = 111.111

    SELECT

    dbo.fn_BRound2(@enumerator/@denominator, 2)

    ,Round(@enumerator/@denominator,2)

    Result:

    .0400 .050000

    Still need to try harder.

  • Sergiy

    SSC Guru

    Points: 109668

    Sorry David,

    error was not on my side.

    Error was in absence of documenting the changes in the function usage.

    It's developer's fault, not user's.

  • Jeff Moden

    SSC Guru

    Points: 993915

    David,

    Just making sure of a nuance to Banker's Rounding... it implies rounding to the nearest penny (hundreths) no matter how many decimal places may be involved... is that correct?  (I know... a bit silly on my part but I just want to be 100% sure.)

     

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993915

    David Jackson,

    Does the function you posted work as described in the code or is there a problem with it... just trying to figure out where THIS thread is gonna go

    --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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • David McFarland

    SSChampion

    Points: 11815

    It can denote rounding to the nearest penny, but it's not really a banking specific function (it's actually not widely used by banks as far as I know, but far more often in applications like digital signal processing, etc.), so it can also round to the nearest thousandths, ten thousandths, even dollars, for that matter.

    The only difference between it and traditional rounding is the handling of values exactly halfway between two possible results, so if rounding to the nearest penny, banker's rounding would kick in with given a given value of 1.045, but not 1.044999999999999, nor 1.0450000000000001. It has to be exactly halfway, otherwise, just use the Round function and you should get the same results.

    There is another difference, but it's more implementational than a directive of the function. Many of the functions available allow you to round things to the nearest nickel (values not denoted by a single decimal position), for instance, which isn't an intended purpose of traditional rounding, even though it's possible to do so. I think that is probably due to the name of the function as much as anything, although with the push to nickels (and their equivalents) being the smallest currency in many places, it has gained some traction.

Viewing 15 posts - 1 through 15 (of 378 total)

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