Hello,
I think I need something complicated:
I have a product table that conteins the day in the month (from 1 to 28 not more) that the customer need to pay his bill (the products is a monthly retainer, exept of one product that is need to be ignored).
I need a sql statment that check all the products records, and if the product meets the following conditions it will insert a debt record in the "debts table", with the next (following) month of the last record and with the "PayDay" value as the day in the month, if it is a new record it suppose to take the actual year and month with the "PayDay".
here are the conditions that need to be TRUE to insert the records:
1. that in the "product table", the product name is not "other"
2. that in the "product table", the product stats is "Active"
3. that in the "debts table", this product do not have any record (this condition will acrros most likely after adding new product)
4. that in the "debts table", the last record (with the newest date) are in status "payed"
product table looks like this:
ProductId, ProductName, ProductStatus, PayDay (like I said, this is the day in the month that the customer need to pay from 1 to 28)
debts table look like this:
DebtsId, ProductId, PayDate, PaymentStatus
in the above tables there is more fields but do not Important to this Q.
I hope there is a nice solution and I was understood
Thank you !!