Value getting Rounded Off.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Value getting Rounded Off..

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-07 : 15:51:44

Hi All,
I have a scenario were i have to calculate a percentage using the below logic
select (@NUM1 * 100)/@NUM2 AS TEST
But if @NUM2 is very large then @NUM1,the result is in percentile (ex: 0.25) is rounded off to zero.But i do not want to get the value rounded.Pleas let me know how can i get this done???
Thanks......

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 16:03:00

Not sure of what your data is, but this should do it. Also not sure how much precision you require:

declare @num1 decimal(18,2)
declare @num2 decimal(18,2)
set @num1=7
set @num2=3

select (@num1 * 100)/@num2

results: 233.3333333333333333

Also consider handling divide by zero errors unless you know the denominator will never be 0...even then, still a good idea to handle it in your code.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-08-07 : 16:45:10

AN integer divided by ant integer is an integer, so 1/2 = 0, 3/2 = 1 etc. So you need a numeric or a
float
select (@NUM1 * 100.0E)/@NUM2 AS TEST

Jim

Everyday I learn something that somebody else already knew

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 17:02:28

Hey K, so using Jim's example, if your data type is already int, then you'd convert it to decimal or float.

select (convert(float, @num1) * 100)/convert(float, @num2)

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-08-07 : 18:14:47

flambaster - nope, you don't need to add any converts. Data type precedence applies here - and as long as one of the values is numeric the result will be numeric. So, the constant 100.0E (don't really need to E here either, just the decimal value) will force the result of the equation to a numeric data type (or decimal).

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 18:20:08

Ah...yeah, I'm not sure why I missed the .0... lol...I do that all the time...just overthinking it I guess.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-07 : 18:24:23

Jim's code would work ok even if @NUM1 and @NUM2 are integers. He is doing a little sleight of hand in his code to force the calculations and the results to be float when he uses that 100.0E. That forces the numerator to floating point, which in turn makes the division a floating point operation.

select (@NUM1 * 100.0E)/@NUM2 AS TEST

We should not beat up on Jim too much for his trickery, because he is being gentle here. Some people try to keep us completely in the dark by using

select (@NUM1 * 100E)/@NUM2 AS TEST

That would work just as well, and to the uninitiated or unobservant, it would look like black magic. I could name names, but I won't

Edit: Darn, I wrote this a while ago and forgot to click "Post". Now it is

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-07 : 21:24:18

Thanks for the reply ..I am getting divide by a zero a error.
Msg 8134, Level 16, State 1, Line 6
Divide by zero error encountered.
Please see sample code

Declare @Rt decimal(18,5),
@samper varchar(max)
set @rt = 1000
If @rt between 100 and 2000000000
Begin
select @samper = (select (2000*100.0E)/''+@rt+'')
print @samper
END

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-07 : 22:33:38

Ok, this should work with exec(@samper)

Declare @Rt decimal(18,5),
@samper varchar(max)
set @rt = 1000

If @rt between 100 and 2000000000
Begin
select @samper = 'select (2000*100.0E)/'+CONVERT(varchar(max), @rt)
exec (@samper)
END;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources