• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

T-SQL calculating percentage changes with nagative numbers

my update statment did not get the correct result in the % changes column
Here is my example table:

table @t
score1st      smallint
score2nd      smallint
chagnes_%      dec(10, 4) -- I also try float


update statment


update t
 set changes_% = ( (score1st - score2nd) / score1st ) * 100
from @t t

data:

score1st      score2nd   % CHANGES
20            20            0%
78            -20            125%
48            35            27%
24            66
17            19
56            38
30            -14
37            70            -89%
73            34
50            13
-20            57
50            20
53            -20
136            31
23            44
-4            220
56            52
56            54

0
jfreeman2010
Asked:
jfreeman2010
  • 5
1 Solution
 
jfreeman2010Author Commented:
the result from the update statement show the %changes column are:


0.0000
1.0000
0.0000
0.0000
0
 
Ephraim WangoyaCommented:

first it should be

(score2nd - score1st) / score1st ) * 100


second
what does a negative score mean
0
 
jfreeman2010Author Commented:
Its mean the score can be negative number.  those just a test number, I can name them as test1 and test1 as the column name.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jfreeman2010Author Commented:
I fixed a problem by doing this:

( (score1st - score2nd) / nullif(convert(decimal(9,2),score1st,0) ) * 100
0
 
TempDBACommented:
It is because when you are dividing the differnce with the score1st, the value coming out is 0.something. or somewholenumber.somenumber like 0.234, 1.2, 2.343,0.09,etc
Since this is smallint, the value obtained is the whole number i.e the first part not the full number.
Further multiplying it gives the wholenumber.

Instead of multiplying the number with 100 after the computation, do it like the following:
((score1st - score2nd)*100.0)/score1st
0
 
jfreeman2010Author Commented:
TempDBA,

yes, it also works the way you suggested. thank you,
0
 
jfreeman2010Author Commented:
very good!!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now