July 27, 2011 at 5:07 pm
Hi,
I am seeking a t-sql solution to produce rounding up the nearest 0.05 cents
The orginal data item is stored in numeric(18,2)
Original 75% Desired Result
17.05 12.7875 12.80
9.15 6.8625 6.90
17.8 13.3500 13.35
20.7 15.5250 15.55
61.85 46.3875 46.40
33.95 25.4625 25.50
17.05 12.7875 12.80
28.05 21.0375 21.05
17.8 13.3500 13.35
18.25 13.6875 13.70
43.35 32.5125 32.55
2.4 1.8000 1.80
I have not come across any google result specifically for t-sql or that does not use excel and the ceiling(x,y) function.
t-SQL Round won't cut it, for example
round( 43.35 * 0.75 , 2 ) returns 32.5100 rather then 32.55
Any suggestions?
July 27, 2011 at 5:37 pm
select (1+CONVERT(INT,@number / 0.05))*0.05
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
July 27, 2011 at 5:46 pm
Hello,
Thanks for your reply.
However, it produces incorrect results for what I am after:
Original 75% Desired (1+CONVERT(INT,< my field name > * 0.75 / 0.05))*0.05
Result
17.05 12.7875 12.80 12.80
9.15 6.8625 6.90 6.90
17.8 13.3500 13.35 13.40 No Should be 13.35 This result should not change
20.7 15.5250 15.55 15.55
61.85 46.3875 46.40 46.40
33.95 25.4625 25.50 25.50
17.05 12.7875 12.80 12.80
28.05 21.0375 21.05 21.05
17.8 13.3500 13.35 13.40
18.25 13.6875 13.70 13.70
43.35 32.5125 32.55 32.55
2.4 1.8000 1.80 1.85 No! Should be 1.80 This result should not change
Close though
July 27, 2011 at 5:53 pm
yeah that was just to check you were testing! :hehe:
SELECT CEILING(@Value / 0.05)*0.05
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
July 27, 2011 at 5:58 pm
Measure twice, cut once.
Yes, that works and thanks for your time.
Solution for me is:
ceiling( ( <my result>) / 0.05 ) * 0.05
eg:
ceiling( (fieldname * 0.75) / 0.05 ) * 0.05
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply