Conversion error..? - 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 2005 Forums

Transact-SQL (2005)

Conversion error..?

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-04-27 : 10:17:31

Hi I'm doing some financial analysis and need to find out incorrect VAT amounts paid:


SELECT Supplier_No,Supplier_Name,VAT_GBP,Calculated VAT_GBP,cast((NET_GBP * 0.175) as decimal(18,2)) as [Calculated VAT_GBP]
from MASTER_TABLE
where convert(decimal(18,2),NET_GBP * 0.175)  <> convert(decimal(18,2), VAT_GBP)

Example of Results:


Supplier_No	Supplier_Name	VAT_GBP	Calculated VAT_GBP
34	Supplier_2	37.82	37.82
56	Supplier_3	38	38

As you can see it does not pull out the correct results. The VAT_GBP and Calcuated VAT_GBP should be different according to my testing and script. Is there a conversion/decmial difference...?

My VAT_GBP and NET_GBP has an original datatype of money (2 decimal places) and i need to compare where VAT_GBP is not 17.5% of NET_GBP.

Any help welcome.

thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-27 : 10:29:01

can you post some sample data from the MASTER_TABLE ?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-04-27 : 10:47:01

Supplier_No Supplier_Name Invoice_No Invoice_Date Gross_GBP Net_GBP VAT_GBP
5136 s1 114950 27/06/2006 -498.8 -498.8 0
5136 s2 245290 26/03/2006 253.96 216.14 37.82
5136 s3 248173 07/05/2006 255.15 217.15 38
5136 s4 248172 30/04/2006 253.96 216.14 37.82
5136 s5 255244 31/07/2006 246.32 209.63 36.69

sorry about the formatting, not sure how to do it in this website..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources