I have two tables Invoice and Payment. I am running a query to get the Invoice balance. Invoice Balance=Invoice table.Invoice Amount-Payment Table.Amount received.
Some times the same invoice has multiple payments and if I want to show each time when an invoice gets paid, I want the query to show the balance, so second time when the invoice gets paid I need to get the balance from Invoice Balance-Amount received, instead of Invoice Amount-Amount Received. How to do this? . I can aggregate the Amount Received and then do the calculations, but, this way I am not able to show each payment date if an invoice has multiple payments. Please help.