Percentage SQL not calculating fields properly

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

General SQL Server Forums

New to SQL Server Programming

Percentage SQL not calculating fields properly

Author

Topic

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-11-28 : 07:42:02

Hi guys,

I have an SQL that tries to calculate the different between two columns and its producing an incorrect outcome:

I get:

-10898.344412 in the field (Variance_in_Percentage)

When I calculate the difference in percentage between:

Forthcoming_Year field: (Example Value): 1826.60504

And

Original_Value field: (Example Value): 1660.8

When I put the same digits as above on this website: http://www.calculatorsoup.com/calculators/algebra/percentagedifference.php

I get: = 9.5088% difference

-----

Could it be to do with decimal points and number of digits returned for the Forthcoming_Year field?

Here is the SQL for the fields:

sum(fm1.feature_quantity * ri1.item_total/si1.pricing_quantity * cic1.bid_rate) as Forthcoming_year

oi1.original_value

sum( oi1.original_value - fm1.feature_quantity * ri1.item_total * cic1.bid_rate)*100.0/nullif(sum(oi1.original_value),0) as Variance_in_Percentage.

---

Any idea what I need to change?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 07:45:43

make it like

sum((fm1.feature_quantity * ri1.item_total)/(si1.pricing_quantity * cic1.bid_rate)) as Forthcoming_year
and

sum( oi1.original_value -( fm1.feature_quantity * ri1.item_total * cic1.bid_rate))*100.0/nullif(sum(oi1.original_value),0) as Variance_in_Percentage.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-11-28 : 07:59:18

quote:
Originally posted by visakh16

make it like

sum((fm1.feature_quantity * ri1.item_total)/(si1.pricing_quantity * cic1.bid_rate)) as Forthcoming_year
and

sum( oi1.original_value -( fm1.feature_quantity * ri1.item_total * cic1.bid_rate))*100.0/nullif(sum(oi1.original_value),0) as Variance_in_Percentage.

Hi Visakh,

I still get the same result (Variance_in_Percentage_2 is the new calculation): http://imageshack.us/photo/my-images/716/outputlm.jpg/

Do I need to change anything?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:05:15

i just noticed something. you're missing si1.pricing_quantity in calculation for variance. why is it so?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Maverick_
Posting Yak Master

107 Posts

Posted - 2011-11-28 : 08:13:51

Ah I missed that field out in my calculation! Sorry

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 08:17:12

yep...include it and try again

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources