Round UP to the nearest 0.05 5 cents

Question

Post reply

Round UP to the nearest 0.05 5 cents

Scott Thornton-407727

SSCrazy

Points: 2271
More actions
July 27, 2011 at 5:07 pm

#241470

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)
Original75%Desired Result
17.0512.787512.80
9.156.86256.90
17.813.350013.35
20.715.525015.55
61.8546.387546.40
33.9525.462525.50
17.0512.787512.80
28.0521.037521.05
17.813.350013.35
18.2513.687513.70
43.3532.512532.55
2.41.80001.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?
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]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

select (1+CONVERT(INT,@number / 0.05))*0.05

MM

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

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 2

Hello,

Thanks for your reply.

However, it produces incorrect results for what I am after:

Original75%Desired (1+CONVERT(INT,< my field name > * 0.75 / 0.05))*0.05

Result

17.0512.787512.8012.80

9.156.86256.906.90

17.813.350013.3513.40No Should be 13.35 This result should not change

20.715.525015.5515.55

61.8546.387546.4046.40

33.9525.462525.5025.50

17.0512.787512.8012.80

28.0521.037521.0521.05

17.813.350013.3513.40

18.2513.687513.7013.70

43.3532.512532.5532.55

2.41.80001.801.85 No! Should be 1.80 This result should not change

Close though

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

yeah that was just to check you were testing! :hehe:

SELECT CEILING(@Value / 0.05)*0.05

MM

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

Scott Thornton-407727 SSCrazy Points: 2271 More actions · Answer 4

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!