Post reply

Rounding numbers

  • May 20, 2011 at 4:38 am

    #238542

    Its a Friday and my brain is having a slow day.

    I am trying to work on a formula that will calculate bonuses using the following formula

    (Numeric(5,4) *Numeric(5,4) * Numeric(18,2)) So that two percentage values and a number.

    This works and returns the correct number, which is good!

    However in another table i store a rounding value that i need to apply. This value could be 10,100,1000 or 10000.

    Say the value produced by the bonus formula was 298886.00 and i had a rounding value of 1000. I would want to see 299000.00, If the rounding number was 10 i would want to see the result of 298890.00.

    I have done this before but i am having a bring block. My initial approach was to do this:

    (Numeric(5,4) *Numeric(5,4) * Numeric(18,2)/RoundingNumber)*RoundingNumber

    But that did nothing. I have tried using this rounding number as an argument in the ROUND function without success.

    Any ideas?

    Thanks

  • May 20, 2011 at 4:57 am

    #1327549

    cast the result and the rounding value as integers, then divide, then multiply.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 5:29 am

    #1327563

    Thanks. Thats almost perfect. However, it always rounds down. How can i get it to round to the nearest?

  • May 20, 2011 at 5:33 am

    #1327568

    EDIT: mistake in code see next post

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 5:39 am

    #1327572

    Sorry, found my mistake

    SELECT ROUND(16499.00,0-LOG10(1000))

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 5:43 am

    #1327573

    SELECT ROUND(16499.00,0-LOG10(1000))

    So its

    SELECT ROUND(Result,0-LOG10(RoundingNumber))

    IS that correct?

    Thanks for all your help

  • May 20, 2011 at 5:51 am

    #1327575

    sperry-750868 (5/20/2011)

    SELECT ROUND(16499.00,0-LOG10(1000))

    So its

    SELECT ROUND(Result,0-LOG10(RoundingNumber))

    IS that correct?

    Thanks for all your help

    Yes, that's right - do you have to handle negatives? If so, it is slightly different because of the way ROUND works...

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 5:51 am

    #1327576

    Mister.magoo, You sir are a beautiful human being. Need to run a few more tests but it all looks good. Thanks a lot!

  • May 20, 2011 at 5:53 am

    #1327578

    sperry-750868 (5/20/2011)

    Mister.magoo, You sir are a beautiful human being. Need to run a few more tests but it all looks good. Thanks a lot!

    Gosh! maybe I should put that in my signature!

    Thank you.

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 5:59 am

    #1327585

    Do with it as you wish. 🙂

    I do not have to deal with negative numbers but it is not to hard to explain, what changes would i have to make?

    Always good to keep for future reference.

  • May 20, 2011 at 6:28 am

    #1327607

    sperry-750868 (5/20/2011)

    Do with it as you wish. 🙂

    I do not have to deal with negative numbers but it is not to hard to explain, what changes would i have to make?

    Always good to keep for future reference.

    Actually, just checking it does work fine with negatives - must have been a brain burp!

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • May 20, 2011 at 7:30 am

    #1327670

    Fantastic. Thanks again!

    • Viewing 12 posts - 1 through 12 (of 12 total)

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

    Cookies on SQLServerCentral

    This website stores cookies on your computer.

    These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

    To find out more about the cookies we use, see our Privacy Policy