VAT

[pkelly]

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?

[#2]

Hi Paul,

You need to use intervalmatch to get what you are looking for here.

Kind Regards

Footsie

[#3]

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

[pkelly]

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

[pkelly]

Ignore the above - Kuldeep obviously read my mind....

[#6]

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