|
I have a site that is working and want to add an administration section to make my life easier, I need to know which customers are due to be invoiced, determined by if the client is new and never been invoiced and the account is more than 10 days old from the date of joining, also if the client is old and already been invoiced one or more times I need to get the last invoice date and add 1 month to that.
I have 2 tables one is (tblcustomers, these columns are what im working with CustID{Primary Key}, DateJoined) the secod table is (tblinvoice, these are all the columns (InvoiceNo{Primary Key, Auto Increment}, AmountNet, AmountVAT, Balace, AmountPaid, DateInvoiced, CustID{Foreign Key}, DatePaid)
Here is the SQl query so far
SELECT cu.CompanyName, inv.InvNo
FROM tblcustomers cu LEFT JOIN tblinvoice inv USING (CustID)
WHERE cu.CompanyName NOT LIKE '%demo%' AND
cu.DateJoined < (CURDATE() - INTERVAL 10 DAY) AND
cu.TermsID='Monthly' AND
(inv.DateInvoiced IS NULL OR inv.DateInvoiced < (CURDATE() - INTERVAL 1 MONTH)))
The problem with this is that If a customer has lets say 4 invoices and they are all 1 month apart under different invoice numbers the first is from January invoice number 1, the second from February invoice number 2, the third from March invoice number 3, the fourth from April invoice number 4, now the account is due to have the 5th invoice for May, but the query returns all the results and I only want the last invoice to show and only if it is 1 month or older
In the end of the day I need a list of accounts 1 month or older that are due to be invoiced regardless of if they have been invoiced before or not without all the duplicate entries and the DISTINCTquery will not let me specify only MAX invoice Numbers.
Any suggestions, I hope it is clear enough to understand.
|
