ansaurus

Question

How Can I Sum Vat in a column in sql server 2008?

Answer 1

+4 A:

 INSERT #ResultTable 
     (NettAmount, GrossAmount, TotalVat)
 SELECT
     NettAmount, GrossAmount, GrossAmount * 17.5 /100
 FROM
     SourceTable

It's unclear what you want to do, sorry...

gbn 2010-08-02 14:37:19

You probably have to round also. It depends on the rules for VAT, but usually you are supposed to round either up or down.

Gabe 2010-08-02 14:41:01

sorry for being unclear.Another example Given a column GrossAmount which has a total across the table of =250 calculate the total vat accross the table (sum) which is at 17.5. Does that help?

2010-08-02 14:50:33

select @VatAmount=SUM(GrossAmount * ( VatRate /100)) from #ResultTable is this correct?

2010-08-02 14:53:54

@devnet247 - no. The Net amount is the value exclusive of VAT, the Gross amount is the value inclusive of VAT - VAT is derived as a proportion of the Net. So to derive the VAT from the gross amount, you need to take into account that the Gross amount already includes VAT. To put it another way: VAT Amount = Gross Amount * VAT Rate / (100 + VAT Rate).

Mark Bannister 2010-08-02 15:18:05

If I were you, I'd have a stored function work out the VAT. The figure shown is subject to change and in any case is not correct for all goods. (Calculation of VAT is in fact a horrifying nightmare as the rates that apply depend on what the goods are and also what they are used for; which, except that the stored function would rapidly assume War-and-Peace proportions, is outside the scope of this website.)

Brian Hooper 2010-08-02 15:19:16

Given a gross amount of 250, VAT at 17.5% is 37.23 and the net amount is 212.77 (to the nearest 0.01). Thanks to the wacky VAT rate, only multiples/simple fractions of gross amounts of 47 produce round net and VAT amounts.

Mark Bannister 2010-08-02 15:32:21

Answer 2

+2 A:

devnet247 - have a 2nd table that contains the valid date tracked VAT rate along the lines of:

vat_rate | vat_type | stt_date | end_date
-----------------------------------------
0.175    | 1        | 20100101 | null


vat_type | description
-----------------------------------------
1        | standard rate
2        | reduced rate
3        | zero rate

and then join on that table where the invoice date is valid for the row. your final sql would be along the lines of

SELECT SUM(NettAmount * vat_rate as total_vat) from #ResultTable r1, vat_table v1 
where r1.invoice_date between v1.stt_date and v1.end_date
and r1.vat_type = v1.vat_type

anyway, if you were tracking the vat that is :)

jim

[edit] - if you were to use a second table, i'd suggest extending that to a 3rd - vat_type table, as vat rates vary across products as well as time. see http://www.hmrc.gov.uk/vat/forms-rates/rates/rates.htm#1

jim 2010-08-02 14:44:46

well, that seemed to go well :-)

jim 2010-08-02 15:19:00

Answer 3

+1 A:

SELECT SUM(GrossAmount) * 17.5 /117.5 AS VATAmount
FROM SourceTable

Bearing in mind that (UK) VAT is due to increase to 20% from January 2011, it would be a good idea to follow Jim's suggestion of a date-tracked VAT rate lookup table.

Mark Bannister 2010-08-02 15:25:29

mark -thank you. i presume it was you that marked me back up. i was tearful at getting down-voted for that :-), but it definately is the best approach - i know from recent commercial experience and as Brian above noted, there are different rates for VAT too, so additional reference would/should be made for that !! cheers...

jim 2010-08-02 15:29:16

Thanks for your suggestion.I already have a look up table I did forget the formula to calculate the vat.Thanks alot

2010-08-02 18:27:16

ansaurus

tags:

views:

answers:

How Can I Sum Vat in a column in sql server 2008?

related questions