x
Solved

# T-SQL calculating percentage changes with nagative numbers

Posted on 2011-03-21
Medium Priority
363 Views
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
Question by:jfreeman2010
• 5

Author Comment

ID: 35182209
the result from the update statement show the %changes column are:

0.0000
1.0000
0.0000
0.0000
0

LVL 32

Expert Comment

ID: 35182211

first it should be

(score2nd - score1st) / score1st ) * 100

second
what does a negative score mean
0

Author Comment

ID: 35182323
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

Author Comment

ID: 35182585
I fixed a problem by doing this:

( (score1st - score2nd) / nullif(convert(decimal(9,2),score1st,0) ) * 100
0

LVL 25

Accepted Solution

TempDBA earned 2000 total points
ID: 35182710
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

Author Comment

ID: 35183909
TempDBA,

yes, it also works the way you suggested. thank you,
0

Author Closing Comment

ID: 35183911
very good!!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month7 days, 20 hours left to enroll

#### 584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.