SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Calculate Percentage difference
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  03:25:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/23/2007 :  03:30:01  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 11/23/2007 :  03:32:57  Show Profile  Reply with Quote
Also make sure values used are of type float
Go to Top of Page

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  03:39:17  Show Profile  Reply with Quote
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.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/23/2007 :  03:52:02  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Make sure you are not missing any parenthesis.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  03:56:51  Show Profile  Reply with Quote
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.
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 11/23/2007 :  04:04:54  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  04:08:24  Show Profile  Reply with Quote
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)
Go to Top of Page

kutumbarao
Starting Member

13 Posts

Posted - 11/23/2007 :  04:13:20  Show Profile  Reply with Quote
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
Go to Top of Page

kutumbarao
Starting Member

13 Posts

Posted - 11/23/2007 :  04:18:40  Show Profile  Reply with Quote
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
Go to Top of Page

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  04:32:19  Show Profile  Reply with Quote
All is working. How do I make percent 25 & not 25.000000000
thanks guys.......
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 11/23/2007 :  04:46:03  Show Profile  Reply with Quote
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.
Go to Top of Page

u2envy1
Yak Posting Veteran

South Africa
77 Posts

Posted - 11/23/2007 :  06:09:28  Show Profile  Reply with Quote
All is working 100% thanks for the quick help.
Much appreciated.
Go to Top of Page

andros30
Yak Posting Veteran

80 Posts

Posted - 12/05/2007 :  12:16:16  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000