Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have two tables...
Table 1 - Sales Invoice Transactions
Inv #, Date, TAXCode, InvoiceValue
1, 30/11/2008, VATSTD, £117.50
2, 01/12/2008, VATSTD, £115.00
3, 01/01/2009, VAT0, £100
Table 2 - TAXCode Lookup
TAXCode, VATRate, ASOfDate
VATSTD, 17.5, 01/01/2001
VAT0, 0, 01/01/2001
VATSTD, 15.0, 01/12/2008
I want to calculate the goods value (Invoice Value - VAT) for my sales invoice transactions in my script.
I want transactions witha a code of VATSTD between 01/01/2001 and 30/11/2008 to apply a rate 17.5 with transactions after this date to apply a rate of 15.
Any ideas?
Hi Paul,
You need to use intervalmatch to get what you are looking for here.
Kind Regards
Footsie
Hi,
here you will have to generate an intermediate table from your taxcode lookup:
try using following code:-
Lookup_Temp:
Load
Taxcode,
Vatrate,
Asofdate
from TexcodeLookup order by Taxcode,Asofdate DESC;
Lookup:
Load
Taxcode,
Vatrate,
Asofdate as StartDate,
date(if(peek(Taxcode)<>NULL,if(peek(Taxcode)=Taxcode,peek(StartDate)-1,today()+1000),today()+1000)) as EndDate
resident Lookup_Temp;
drop table Lookup_Temp;
Now you will be having taxcode, vatrate and the effective startdate and enddate.
Now you can use an intervalmatch between this new table and your transaction table based on invoice date.
Thanks & Best Regards,
Kuldeep Tak
Hi Footsie
Will this work on my data table?
I do not have a from / to date on the one record just an AsOfDate.
e.g.
TaxCode, TaxRate, AsOfDate
VATSTD, 17.5, 01/01/2001
VATSTD. 15.0, 01/12/2008
If not, is there anyway I can get my table to fit (programatically) i.e. end up with...
TaxCode, TaxRate, AsOfDate, EndDate
VATSTD, 17.5, 01/01/2001, 30/11/2008
VATSTD, 15.0, 01/12/2008, Todays Date
Ignore the above - Kuldeep obviously read my mind....
Dear Paul,
Please check the code i have given. Using this you will be able to get the desired table.
Thanks & Best Regards,
Kuldeep Tak