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.