SQL Server Performance

ROUND function

Discussion in 'SQL Server 2005 General Developer Questions' started by kingofSQL, Nov 16, 2006.

  1. kingofSQL New Member

    SELECT<br />round(9.425000,2,1),<br />round(34.775000,2,1)<br /><br />returns:<br />9.420000<br />34.770000<br /><br />However I would like to use the same function for both values to return <br />9.420000<br />34.780000<br />(or 9.42 and 34.7<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />Any suggestions?<br /><br />as always thanks all for your help [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /> <br /><br /><br /><br />it's all good
  2. FrankKalis Moderator

    Is it this what you're looking for?



    SELECT ROUND(MyNumber,2,1)
    , CASE
    WHEN ROUND(MyNumber * POWER( 10, 1 + 1 ) ,0,1) - (ROUND(MyNumber * POWER(10, 1 ) ,0,1) * 10) <= 5
    THEN ROUND(MyNumber,2,1)
    ELSE ROUND(MyNumber + 0.01, 2,1)
    END
    FROM
    (SELECT 9.425000 UNION ALL SELECT 34.775000) AS t1 (MyNumber)



    ---------- -----------
    9.420000 9.420000
    34.770000 34.780000

    (2 row(s) affected)


    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  3. Madhivanan Moderator

    Where do you want to show data? If you use front end application, you can easily truncate leading zeroes

    Madhivanan

    Failing to plan is Planning to fail
  4. FrankKalis Moderator

    Madhivanan, look closely at the values of the output again. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />It's not about truncating anything. To me it seems like a variation of Bankers Rounding.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  5. Madhivanan Moderator

    Yes.<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by kingofSQL</i><br /><br />SELECT<br />round(9.425000,2,1),<br />round(34.775000,2,1)<br /><br />returns:<br />9.420000<br />34.770000<br /><br />However I would like to use the same function for both values to return <br />9.420000<br />34.780000<br /><font color="red">(or 9.42 and 34.7<img src='/community/emoticons/emotion-11.gif' alt='8)' /></font id="red"><br /><br />Any suggestions?<br /><br />as always thanks all for your help [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /> <br /><br /><br /><br />it's all good<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />I considered that red part [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />Also I am not sure why first value is 9.420000 or 9.42 instead of 9.430000 or 9.43<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  6. kingofSQL New Member

    Thanks FrankKalis/Madhivanan the code above works <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />fyi...<br />My project is to store data in one place but output the SAME data to many front ends. (web, design tools, reports). When I compare the data between the web and the other front end I'm sometimes off by a penny because of the rounding. (My front end is Quark with Xcatalog extension to pull in the data via ODBC and Quark/Xcatalog does not have a code layer that I can perform an ETL type process.)<br /><br />However I'm one those folks who would rather do all of the ETL on the DATABASE BACKEND and not in the front end code to avoid this very situation <br /><img src='/community/emoticons/emotion-5.gif' alt=';-)' /> <br /> <br />I was also told to try Modulus? This term describes the result when one number is divided by another number resulting in a remainder. For example 4 % 3 would result with a value of 1 since 1 is left over after 4 is divided by 3.<br /><br /><br />7%7 = 0<br />4%3 = 1<br /><br /><br /><br />it's all good
  7. FrankKalis Moderator

    Go back to the raw values<br /><br />9.425000<br />34.775000<br /><br />The first has a digit &lt; 5 before the last significant digit, the latter has a digit &gt; 5 right before the last number. So, to me it looks as if the first should be rounded down, while the second needs to be rounded up. And that is some variation of Bankers Rounding. Not exactly, but pretty close. Bankers Rounding, however, is much simpler to implement. Just use STR(). [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. kingofSQL New Member

    Yes this is a variation of Bankers Rounding.<br /><br />CURSES Bankers Rounding has struck again!!!!! <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />it's all good
  9. FrankKalis Moderator

    Oops, my reply was for Madhivanan. So, you found your solution? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  10. kingofSQL New Member

    Yes using your code but do you think a Bankers Rounding solution would work?

    it's all good
  11. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />Oops, my reply was for Madhivanan. So, you found your solution? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Thanks Frank [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  12. aledmb New Member

    hi,

    i'm searching for a way to do banker's rounding
    on a stored procedure, but i'm stuck on this
    problem (follows the code).

    declare
    @v1 as float,
    @v2 as float

    set @v1 = 0.3650
    set @v2 = 1.3650

    print str(@v1, 10, 2)
    print str(@v2, 10, 2)

    this code prints 0.36 and 1.37.
    does anyone know why?
    thanks in advance!
  13. MohammedU New Member

  14. aledmb New Member

    Thank you Mohammed,

    In fact I'm using that function posted there
    to do banker's rounding, but did you saw that
    that was happening using the str() function?

    it's output varies depending on the whole
    part of the number passed to it.

    thanks!

Share This Page