I have a Table AMTPAYABLE which has Following Fields
Accountno, Invoiceno,n_Month,amountpayable.All the fields are Numeric.
N_month filed represents period which is like following
2012.1 represents January2012,
2012.2 represents Feb2012
2012.9 sep2012
2012.11 Oct2012
2012.12 Nov2012
2012.13 Dec2012
Each Months Bill say Dec-2012 will have a unique Invoice No. thus my table amtpayable will have 12 Bills in a year for each accountno. Like wise My above DBF file has 5-6 years Data of over 50000 customers
Now I been asked to compare the Bills of each month with the preceding 12 Months average bill and report it.
Now suppose I choose DEC-2012, I have to compare ampountpayable for DEC-2012 with the average Bills of DEC-2011 to NOV-2012.
What SQL will be used to get the required Data from the entire Table if I need Data In following format
Accountno. CurrInvoice CurrInvoiceNo CurrAMT AVGAMT DIFFAMT
Another SQL that I require is if I want to Get records for last 13 Months or 14 Months before say N_month is less than 2011.2. for all accountno.
Regards
Harsh