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
 General SQL Server Forums
 New to SQL Server Programming
 Percentage SQL not calculating fields properly
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Maverick_
Posting Yak Master

107 Posts

Posted - 11/28/2011 :  07:42:02  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 11/28/2011 :  07:45:43  Show Profile  Reply with Quote
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/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 11/28/2011 :  07:59:18  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2011 :  08:05:15  Show Profile  Reply with Quote
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/

Go to Top of Page

Maverick_
Posting Yak Master

107 Posts

Posted - 11/28/2011 :  08:13:51  Show Profile  Reply with Quote
Ah I missed that field out in my calculation! Sorry
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2011 :  08:17:12  Show Profile  Reply with Quote
yep...include it and try again

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

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.19 seconds. Powered By: Snitz Forums 2000