Rounding Functions (with T-SQL)

Everybody knows floor and ceiling function.
They are same as in any other API.
Hope you already know those terms and how to use it.
And as you know floor and ceiling functions, you must be familiar the 'round' function.
Let that roam around 'round' first.
Round accepts two arguments, first as the number which you want to round and the other as to the decimal point upto you want to round.
round( input number, decimal point to round)
So input output will be as show as below:
round(136.84,2) -> 136.84
round(136.84,1) -> 136.80
round(136.84,0) -> 137.00
round(136.84,-1) -> 140.00
And you can go so on...

Indian currency is Rupee (INR). All transactions are stored in rupee.
Paise is 100th part of Rupee. And when we round money, we have to round to 25 paise or 50 paise. So simple round function shown above will never work. You have to write you own. Wait for a while , I will tell you what you can write.

Take another case. Suppose you are a shopkeeper and in order to be nice to your customer, you don't want to round merely 100.10 to 101 or 100.5. On the other hand, you cannot leave 99 paise on the a product of 97.99 rupees. So as a shop keeper, you will always love to provide rule for rounding. i.e. Round to lower digit upto 70 paise and round upward if it's more than 70 paisa.
Logic seems bigger but this can be achieved by just one multiplication, addition and devision.

For T-SQL,

declare @rnd_amt decimal(6,2)
declare @devide decimal(11,9)
set @rnd_amt = 1 -- Rounding amount.. by Rupee 1. 100 should be multiple of @rnd_amt.

-- Use any one of these three value of @devide according to you need i.e. round, floor, ceiling
set @devide = 0.00 -- Round to Floor
set @devide = 0.9999999 -- Roudn to Ceiling
set @devide = 0.70 -- Nearest Rounding. Devide. In above example it's 0.70 i.e.70% . So 70 paise or more than that will be rounded upward.
print floor(58.00001/@rnd_amt + @devide)*@rnd_amt

e.g.
print floor(58.7455698/1+.70)*1

Easy huh!!