Calculate Percentage difference

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Calculate Percentage difference

Author

Topic

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 03:25:41

How do I calculate the percentage difference between two numbers.
Number 1 = 10
Number 2 = 8
10-8 = 2
2 / 8 = .25
.25 * 100 = 25

This is what I would like to do with values from two tables.
This is what I have.

SELECT Key,
(NULLIF(b.Num1,0) - NULLIF(a.Num2,0) / NULLIF(a.Num2,0) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key2

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 03:30:01

Replace NULLIF() with ISNULL(). Mind also divide by zero problem.

SELECT Key,
(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / ISNULL(a.Num2,1) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-11-23 : 03:32:57

Also make sure values used are of type float

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 03:39:17

Im getting the following error. I think it is because of type int.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

How do I change the type int to type float in stored procedure.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 03:52:02

Make sure you are not missing any parenthesis.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 03:56:51

All is good. I was missing a (
I get the following now
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-23 : 04:04:54

I believe you have tendancy to overlook suggestions. Divide by zero error....that's precisely what I warned you against in my first reply.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 04:08:24

This is also returning zero values

SELECT Key,
(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / ISNULL(a.Num2,1) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key
Not the correct values. I think the * 100 is calc before the / ISNULL(a.Num2,1)

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-23 : 04:13:20

Hi Please try with this

SELECT Key,
(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,1)) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-23 : 04:18:40

Plese remove ISNULL of 1 condition, the best way to check the Zero in the WHERE
Ex:
SELECT Key,
(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,0)) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key
WHERE ISNULL(a.Num2,0) <> 0

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 04:32:19

All is working. How do I make percent 25 & not 25.000000000
thanks guys.......

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-23 : 04:46:03

Convert(decimal(18,2),(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,0)) * 100) as Percent

Tghis will give you a percent to 2 decimal places.

u2envy1
Yak Posting Veteran

77 Posts

Posted - 2007-11-23 : 06:09:28

All is working 100% thanks for the quick help.
Much appreciated.

andros30
Yak Posting Veteran

80 Posts

Posted - 2007-12-05 : 12:16:16

quote:
Originally posted by kutumbarao

Plese remove ISNULL of 1 condition, the best way to check the Zero in the WHERE
Ex:
SELECT Key,
(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,0)) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key
WHERE ISNULL(a.Num2,0) <> 0

Ok here is the issue, I need to show only results where the % change from one amount to another is > 20%. i.e. amt1 = 30, amt2 = 55. I should expect a 45% change in this example.
Is the following syntax correct?

Select Key,
ABS(ISNULL(b.amt1,0) - ISNULL(a.amt2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.amt2,0)) * 100 AS Percent
FROM Table1 AS a INNER JOIN Table2 AS b ON a.Key = b.Key
WHERE ISNULL(a.amt2,0) <> 0

And would this give me the same result?
Select Key, amt1,
ABS(amt1 * .2) + amt1 as 'Over20'
INTO Table2
FROM Table1

Select Key,
b.amt1 as 'PreviousAmt', a.amt2 as 'CurrentAmt'
FROM Table1 a INNER JOIN Table2 b ON a.Key = b.Key
WHERE a.amt2 > b.Over20

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources