I'm trying to create a SQL View that I can use in a report which will look at an Open Invoices Table for a given customer by Document Date while also looking at Cash Receipts/Payments Table from the Customer and applying the Payments to the Open Invoices based on the Invoice Date. The goal is to provide an overview of how a customers payment(s) are being applied to their open invoices.
Below is an example of Table: OpenInvoices and Table: Payments and then a running/reducing balance. I can easily order the invoices and payments but I'm having a tough time getting some type of running/remaining balance on the payments as they are being subtracted from the Invoices...
|INV002398||9/3/2018||$ 425.13||PYM0002682||$ 425.13||$ -|
|SVC000736||9/17/2018||$ 4,158.60||PYM0002682||$ 4,158.60||$ -|
|INV004283||10/6/2018||$ 6,754.55||PYM0002682||$ 1,916.27||$ 4,838.28|
|INV004283||10/6/2018||$ 6,754.55||PYM0002974||$ 1,500.00||$ 3,338.28|
|INV004283||10/6/2018||$ 6,754.55||PYM0003118||$ 3,338.28||$ -|
|RPG000657||10/11/2018||$ 8,745.92||PYM0003118||$ 461.72||$ 8,284.20|