forumla deciamal places - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

forumla deciamal places

Author

Topic

token
Posting Yak Master

133 Posts

Posted - 2005-11-22 : 19:02:30

hi,

I have created a Forumla for one of my fields that calculates the VAT (tax rate) on a product and then places a new value with the tax rate included in another field:

([UnitPriceExVat] / 100 * 17.5 + [UnitPriceExVat])

17.5 is the tax rate. So essentially it finds out what the tax rate is, and then adds that value to the original price. This value is then inserted in a field called "UnitPriceIncVat".

However the value it enters into the "UnitPriceIncVat" field has 4 decimal places. I only want 2 decimal places because it is the price of a product, not a math number.

Any ideas on how to restrict it to 2 decimal places using EM?

Cheers,
token

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-22 : 19:32:05

I think your calculation method will give inaccurate results because of rounding of intermediate results. This simpler calculation will be more accurate:
[UnitPriceExVat] * 1.175

The likely reason you are getting 4 decimal places is that [UnitPriceExVat] in a MONEY column, which is 4 decimal places.

You can use the ROUND function to round off to 2 decimal places:
round( [UnitPriceExVat] * 1.175 , 2 )

This shows the results of your calculation, my calculation, and my calculation rounded:


elect
	 [UnitPriceExVat] ,
	[Your Calculation] = 
	([UnitPriceExVat] / 100 * 17.5 + [UnitPriceExVat]),
	[My Calculation] = 
 	[UnitPriceExVat] * 1.175,
	[Rounded Calculation] = 
	round( [UnitPriceExVat] * 1.175 , 2 )
from
	(select  [UnitPriceExVat] = convert(money,333.3333)  ) a



UnitPriceExVat        Your Calculation           My Calculation             Rounded Calculation        
--------------------- -------------------------- -------------------------- -------------------------- 
333.3333              391.66605                  391.6666275                391.6700000

(1 row(s) affected)

CODO ERGO SUM

token
Posting Yak Master

133 Posts

Posted - 2005-11-23 : 05:41:13

thanks for the reply! it really helped me out!

I was wondering where I can learn how to make forumlas? I have two books on SQL server but neither of them cover formulas.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources