?
Solved

T-SQL calculating percentage changes with nagative numbers

Posted on 2011-03-21
7
Medium Priority
?
356 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Ephraim Wangoya
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 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

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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