I have many SQL statements that include something like;
'....Sum(If([vat],if([weekendingdate] Between '04/01/2011' and '31/12/2099', [takings]/1.2,if([weekendingdate] BETWEEN '30/11/2008' and '01/01/2010',[takings]/1.15,[takings]/1.175)),[takings])) AS Total.....'
dealing with the different periods of VAT rates.
This is a real pain as I have to change all the statements whenever the rate changes so the statements get bigger and bigger.
I want to implement a VAT Rates table with rates and dates so that only onle place needs updating.
However, I'm getting stuck with the approach to a query checking the transaction date against the VAT table.
considering the 17.5% rate went out of date soem two years ago....
you should have at least 4 VAT codes
(zero rated, exempt, fuel and standard rate)
you probably also ought tio have a VAT rate table which defines what those rates are. however the problem is how you model the actual VAT rate applicable to a specific line item on an invoice
personally Im a fan of copying the then VAT rate as part of the invoice line item. that means you have the audit trail you have the copy of what was sent tot he customer, if you use a VAT code then its possible that changes may be made to the VAT code over time.
the invoice is avtually a legal document between you and the customer and your and HMRC. so saving a specific copy of the data as at invoice time is in my books a smart call, even if at first glance it may break normalisation rules. I'd argue it doesn't break normalisation and you need the tracability if what was invoiced and then what happneed to that invoice over time. it also helps if you have an electronic copy that you can fire off to reclcitrant payers when they use the 'can't find the invoice' excuse.
Is taking part in the 'Distinguished Gentlemans' ride. Being neither distinguished nor a gentleman, but I am keen to raise funds for Prostrate Cancer research. At present I'm threatening to ride a Norton 961 wearing a kilt. If you would like to contribute to Prostrate Cancer research then please consider donating via this page:- http://www.gentlemansride.com/rider/healdem
Thanks for this, however, I'm dealing with Till transactions, and all I know is the total is with or without VAT. No need to worry about different rtates of VAT per transaction, only when they occurred.