Converting Int to decimal

[zeeshanmbutt]

Hi all,

I have a Table T1 which has the following 2 fields:
Units (int type), CatUnits (int type)

T1 may has following records
Units, CatUnits
192237,2510116
242537,2947050
128112,2183856


Now I want to get the % Difference of Units Vs CatUnits, which I can achieve by the following formula:
(Units/CatUnits)*100
I would add this foruma as 'PercentDiff' in my following select statement:

select Units,CatUnits,((b.Totalunits/a.TotalUnits)*100)PercentDiff
from T1
192237,2510116,0
242537,2947050,0
128112,2183856,0

Why I am getting 0 here, that is i am getting only the integer part. How can i convert it into percent with one decimal?

Thanks a million in advance.....

 

[zeeshanmbutt]

How can I get this:

Units, CatUnits,PercentDiff
192237'2510116,7.7
242537,2947050,8.3
128112,2183856,5.9

 

[gmmastros]

You are getting integer math. In order to make this work, you need to convert to decimal before doing the math operations.

[COLOR=blue]select[/color] Units,CatUnits,[COLOR=#FF00FF]Convert[/color]([COLOR=blue]Decimal[/color](10,1), ((1.0 * b.Totalunits/a.TotalUnits)*100))PercentDiff

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[zeeshanmbutt]

gmmastros ,

Great it works...
But can you explain me how its working?
a)What this part does: (why we r multiplying it by 1):
(1.0 * b.Totalunits/a.TotalUnits)

b) What & how the convert part works?


Also, in the same table T1 let say I have the following decimal field let (with Precision 18 & Scale 2)
Dollars(decimal type)

And I want to get only the int part (with rounding)...
How can I achieve this?....

Thanks a million

 

[gmmastros]

Sure. I'll explain.

When performing math operations, SQL Server first checks the data types and will promote it to the data type of higher precedence. Initially, all of the calculations involved integers, so the result would also be expressed as an integer. By multiplying by 1.0 (which has a data type of real or float, i forget which one), it causes the result to be a real or float. You could have accomplished the same thing by converting each of the field and then performing the calculations.

Let me show you some examples.

[COLOR=green]-- Both integers, so result is integer
[/color][COLOR=blue]Select[/color] 192237/2510116

[COLOR=green]-- Multiply by a float, so result is float
[/color][COLOR=blue]Select[/color] 100.0 * 192237/2510116

[COLOR=green]-- Convert to decimal (scale=1) to round the number
[/color][COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]Decimal[/color](10,1), 100.0 * 192237/2510116)

You can use the Round function to round a number. If you don't convert the number to int, it will still display the decimal places, but they will be 0's because the number is rounded.

[COLOR=blue]Declare[/color] @Value [COLOR=blue]Decimal[/color](18,2)

[COLOR=blue]Set[/color] @Value = 101.5

[COLOR=blue]Select[/color] [COLOR=#FF00FF]Round[/color](@Value, 0)
[COLOR=blue]Select[/color] [COLOR=#FF00FF]Convert[/color]([COLOR=blue]int[/color], [COLOR=#FF00FF]Round[/color](@Value, 0))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom