SQL Server Problem - 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

SQL Server Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-05 : 10:11:07

Ozair writes "Hi, I am using SQL Server 2000 Standard and the Operating system is Windows Server 2003, Standard Edition.

The problem is that when ever there is a division operation and the result is less than 1 like for example 0.123, it treats it as a whole number. So, the result of the division operation is 0.

Offered = 10335
Carried = 7330

Formula = 100 * ( 1 - (carried/offered))
This formula is used in the command:

str(100*( (1)-((carried)/(offered))),9,2)as Result,

If the above statement is executed it gives the output 100.
While if you calculate it manually it's 29.07.

Your help would be highly appreciated"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-05 : 10:27:44

[code]str(100*( (1)-((carried * 1.0)/(offered))),9,2)as Result[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-05 : 10:33:48

Also you dont need to use Str function

Select 100*( (1)-((carried)/(offered)),9,2) as Result

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-06 : 02:12:41

reason:

int/int=int
int/decimal=decimal
decimal/int=decimal

so:

if carried and offered are both int then you get 0 if they're decimal you'll get a fraction

use proper datatypes

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources