October 14, 2009 at 12:55 pm
I have been struggling with this one... I'm trying to figure out a general purpose t-sql function that will allow me to round up to the 'nearest' specified increment ... .. such as
pass 3.98 and 5 to the function and get back 5
pass 5.0 and 5 to the function and get back 5
pass 5.01 and 5 to the function and get back 10
pass 101.4 and 100 to the function and get back 200
pass 2000 and 1000 to the function and get back 2000
pass 1670 and 1000 to the function and get back 2000
etc.
I've messed around with floor and ceiling rounding ... which seems to work to the nearest whole value ... but not to the next
'tier'. Maybe I'm missing something. I have kludged this with a not so elegant lookup to a physical table ... but it really seems like a UDF would be a lot easier and faster.
Any solutions would be greatly appreciated.
October 14, 2009 at 1:54 pm
should be simple maths
ie
ceil(1670/1000)*1000 = 2000
ceil(101.4 /100)*100 = 200
October 14, 2009 at 3:20 pm
Thanks! That did the trick for me. I don't know why I couldn't figure out the ceiling/floor deal...but this is exactly what I needed.
October 14, 2009 at 3:30 pm
quadrant.systems (10/14/2009)
Thanks! That did the trick for me. I don't know why I couldn't figure out the ceiling/floor deal...but this is exactly what I needed.
Do be mindful of ceiling and floor if you happen to deal with negative amounts.
select ceiling(4.5),ceiling(-4.5)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 15, 2009 at 2:09 am
Also be mindful of the overhead
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply