[RESOLVED] Rounding Problem.

[Bill Crawley]

Hi All,

I'm using SQLServer 2005.

In my SP I have an internal table with one of the columns declared as int.

As part of the insert statement, the value going into this column uses a simple formula of @Print_run/100 * @percentage.

Where my first iteration becomes:

1/100 * 75 = 0.75

So in my internal table, I want this rounded to 1, but 0 is entered.

I have tried all types of 'round' on the formula, but it makes no difference.

I have also tried Casting to Numeric before rounding, but that makes no difference either. Can anyone show me what I need to apply to my formula to get it to round correctly.

[olivthill2]

Maybe, just adding 0.50 before rounding should do the trick.

[Bill Crawley]

this will not work. If my original answer is 1.25 then rounding I'd expect to see 1 adding 0.5 will give 1.75 before rounding causing the rounding to go to 2.

[ComITSolutions]

How about using round function?

[Bill Crawley]

Yes, sorry I didn't make it that clear in my original question, but when I said

I have tried all types of 'round' on the formula I was refering to the Round function.

it seems to truncate rather than round.

[ComITSolutions]

select 1/100*75 as Result -- since all the operands are interger result is also integer
result
0



select 1.0/100*75 as Result
result
0.750000



select Round(1.0/100*75,1) as result -- Rounding off to 1 digit after decimal point
result
0.800000




select Round(1.0/100*75,0) as result -- Rounding off to 0 digit after decimal point i.e. Round Number
result
1.000000



Post your code.

[Alsvha]

Wouldn't CEILING solve your problem?
CEILING(0.75) returns 1