If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > MySQL > Matching historical tranactions against VAT dates

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Oct 2011
Posts: 8
Matching historical tranactions against VAT dates

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.

Reply With Quote
  #2 (permalink)  
Jaded Developer
Join Date: Nov 2004
Location: out on a limb
Posts: 12,286
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:-
Reply With Quote
  #3 (permalink)  
Registered User
Join Date: Oct 2011
Posts: 8
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
Reply With Quote
  #4 (permalink)  
Registered User
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 860
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.
Ronan Cashell
Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On