I have a query that needs to be written:
I have a transaction table where sales are placed. I have a payment table where payments are received and are linked by a bookingNo (numeric field)
I have a report that is run periodically that has a from and a to date range (user selected)
This has been running very well when there is one transaction and one payment, or many transaction parts and one payment.
I now have a situation where a transaction (made up of 1 or more items) can have 2 or more payments assigned to it. (part payments over a time period).
I need run an sql query where the income from any date range (user entered) obtains the transaction total from the sales table, then finds any payments before a payment found in the report date range (user selected to /from date range) - then subtracts any previously found part payments from the total to arrive at a current balance. After this is done, any current part payments made in the report date range are subtracted from the remaining balance to arrive at a new balance.
The issue is that in the payment table - a payment date is recorded for each payment made. How do I select all payments before a certain date from the payment table and subtotal them, then subtract the payment sub total from the sales transaction table to arrive at a balance owing?
record id 8
I need to get a query to obtain all subtotals for the sales and purchases tables and subtract one from the other to show a total owing.