Solved

T-SQL calculating percentage changes with nagative numbers

Posted on 2011-03-21
7
350 Views
Last Modified: 2012-05-11
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
Comment
Question by:jfreeman2010
  • 5
7 Comments
 

Author Comment

by:jfreeman2010
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

by:ewangoya
ID: 35182211

first it should be

(score2nd - score1st) / score1st ) * 100


second
what does a negative score mean
0
 

Author Comment

by:jfreeman2010
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:jfreeman2010
ID: 35182585
I fixed a problem by doing this:

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

Accepted Solution

by:
TempDBA earned 500 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

by:jfreeman2010
ID: 35183909
TempDBA,

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

Author Closing Comment

by:jfreeman2010
ID: 35183911
very good!!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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

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

Join & Ask a Question