Solved

T-SQL calculating percentage changes with nagative numbers

Posted on 2011-03-21
7
349 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now