### Rounding Up Prices

The problem was posted by Steve Ledbetter:

I am trying to do some slightly complicated rounding in transact SQL in a

stored proc. I need to round UP, and have implemented this for straight

rounding up, for rounding up to nearest nickel/dime/quarter/half dollar, and

rounding up to next .49/.99 using addition and CEILING and formulas..

My difficulty is that in addition to the above options I need the option to

allow the user to select two values to round to. Example, user selects

'.03' as 'low value' and '.07' as 'high value', I need to round up to the

next .x3 or .x7, so that 1.22 would become 1.23, 1.25 would become 1.27,

1.29 would become 1.33, etc. The same idea would apply to dollars

separately, so picking .03 and .07 for cents, and 5 and 8 for dollars: 3.21

would round to 5.23 ($3 would round up to $5 .21 would round up to .23 -

the neareast .03), 6.35 would round to 8.37, 9.99 would round to 15.03,

etc.

It's for a retail products management module where clients can generate

their selling prices from costs (product cost, freight, installation cost,

etc) using formulas and rounding options (like rounding up to nearest .99.

.49 , nickel, dime, etc. or to a user defined pair of numbers (which was

what spurred my questions). I'll be updating 10's of thousands of records

at a time and needed to do it in a stored proc for speed.

My solution:

You can round up dollars as follows:

DECLARE @numberToRoundUp INT

SET @numberToRoundUp = 9

------- Assuming Numbers table stores numbers from 1 to 1000

SELECT TOP 1 Number FROM Numbers

WHERE Number % 10 IN(5,8) AND Number > @numberToRoundUp

ORDER BY Number

You can round up cents in the same way.