May 20, 2011 at 4:38 am
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
cast the result and the rounding value as integers, then divide, then multiply.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 5:29 am
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
EDIT: mistake in code see next post
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 5:39 am
Sorry, found my mistake
SELECT ROUND(16499.00,0-LOG10(1000))
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 20, 2011 at 5:43 am
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
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);May 20, 2011 at 5:51 am
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
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);May 20, 2011 at 5:59 am
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
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);May 20, 2011 at 7:30 am
Fantastic. Thanks again!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply