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.

Any help would be appreciated.

Regards
Pete

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.

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.

Many thanks
Pete

You should closely at your data model. Perhaps it would be better to store the VAT amount with each record rather than attempting to calculate it based on periods.