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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ
 All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Calculate Percentage difference Reply to Topic  Printer Friendly
Author  Topic

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  03:25:41 How do I calculate the percentage difference between two numbers.Number 1 = 10Number 2 = 810-8 = 22 / 8 = .25.25 * 100 = 25This 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 PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key2

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

 Posted - 11/23/2007 :  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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 11/23/2007 :  03:32:57 Also make sure values used are of type float

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  03:39:17 Im getting the following error. I think it is because of type int.Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'AS'.How do I change the type int to type float in stored procedure.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

 Posted - 11/23/2007 :  03:52:02 Make sure you are not missing any parenthesis.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  03:56:51 All is good. I was missing a (I get the following nowMsg 8134, Level 16, State 1, Line 1Divide by zero error encountered.

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

 Posted - 11/23/2007 :  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 AthalyeIndia."The IMPOSSIBLE is often UNTRIED"

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  04:08:24 This is also returning zero valuesSELECT Key,(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / ISNULL(a.Num2,1) * 100 AS PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.KeyNot the correct values. I think the * 100 is calc before the / ISNULL(a.Num2,1)

kutumbarao
Starting Member

13 Posts

 Posted - 11/23/2007 :  04:13:20 Hi Please try with thisSELECT Key,(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,1)) * 100 AS PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.Key

kutumbarao
Starting Member

13 Posts

 Posted - 11/23/2007 :  04:18:40 Plese remove ISNULL of 1 condition, the best way to check the Zero in the WHEREEx:SELECT Key,(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,0)) * 100 AS PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.KeyWHERE ISNULL(a.Num2,0) <> 0

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  04:32:19 All is working. How do I make percent 25 & not 25.000000000thanks guys.......

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

 Posted - 11/23/2007 :  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 PercentTghis will give you a percent to 2 decimal places.

u2envy1
Yak Posting Veteran

South Africa
77 Posts

 Posted - 11/23/2007 :  06:09:28 All is working 100% thanks for the quick help.Much appreciated.

andros30
Yak Posting Veteran

80 Posts

 Posted - 12/05/2007 :  12:16:16 quote:Originally posted by kutumbaraoPlese remove ISNULL of 1 condition, the best way to check the Zero in the WHEREEx:SELECT Key,(ISNULL(b.Num1,0) - ISNULL(a.Num2,0)) / CONVERT(NUMERIC(18,2),ISNULL(a.Num2,0)) * 100 AS PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key1 = b.KeyWHERE ISNULL(a.Num2,0) <> 0Ok 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 PercentFROM Table1 AS a INNER JOIN Table2 AS b ON a.Key = b.KeyWHERE ISNULL(a.amt2,0) <> 0And would this give me the same result?Select Key, amt1,ABS(amt1 * .2) + amt1 as 'Over20'INTO Table2FROM Table1Select Key,b.amt1 as 'PreviousAmt', a.amt2 as 'CurrentAmt'FROM Table1 a INNER JOIN Table2 b ON a.Key = b.KeyWHERE a.amt2 > b.Over20
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC