SQLTeam.com | Weblogs | Forums

SQL View Order By and Running Total?


#1

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

#2

hi

I hope this link helps

if you are not able to
i will take a shot at it
:slight_smile:
:slight_smile:

Thanks