Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    10

    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.

    Regards
    Pete

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    12,446
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2011
    Posts
    10
    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

  4. #4
    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)
    http://www.it-iss.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •