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...
| InvoiceNbr | InvoiceDate | InvoiceBalance | |||
|---|---|---|---|---|---|
| INV002398 | 9/3/2018 | $ 425.13 | |||
| SVC000736 | 9/17/2018 | $ 4,158.60 | |||
| INV004283 | 10/6/2018 | $ 6,754.55 | |||
| RPG000657 | 10/11/2018 | $ 8,745.92 | |||
| PaymentNbr | PaymentDate | PaymentAmount | |||
| PYM0002682 | 10/8/2018 | $ 6,500.00 | |||
| PYM0002974 | 10/10/2018 | $ 1,500.00 | |||
| PYM0003118 | 10/13/2018 | $ 3,800.00 | |||
| Desired Results | |||||
| InvoiceNbr | InvoiceDate | OriginalInvoiceAmount | PaymentNbr | AppliedAmount | RemainingInvoiceBalance |
| 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 |
| InvoiceNbr | InvoiceDate | RemainingBalance | |||
| RPG000657 | 10/11/2018 | $ 8,284.20 |
