Add column for a Running total for PO Amount Remaining or Outstanding Balance yet to be Invoiced

[awaria]

The result set has detailed and header data elements related to PO and Invoices.
Requested to have column added to results set for PO_Amt_Remaining.

Not sure how to accomplish that data request. The result set has over 40 columns so I didn't know how to, or want to group on all those columns, but, will try any recommendations.

Query run after invoice 3:

PO_Nbr PO_Amt Inv_Nbr Inv_Dt Inv_Amt PO_Amt_Remaining

1010 $1,000 1 1/1/18 $100 $500
1010 $1,000 2 2/1/18 $100 $500
1010 $1,000 3 3/1/18 $300 $500

as another invoice assoc with PO is paid , the PO_Amt_Remaining would decrement for all invoices in the record set when the query is executed after invoice 4

PO_Nbr PO_Amt Inv_Nbr Inv_Dt Inv_Amt PO_Amt_Remaining

1010 $1,000 1 1/1/18 $100 $300
1010 $1,000 2 2/1/18 $100 $300
1010 $1,000 3 3/1/18 $300 $300
1010 $1,000 4 4/1/18 $200 $300

Thank you, awaria

 

[IanWaterman]

Join a Sub Query that sums the INv_Amt and groups by PO_Nbr.
You can then join this subquery to your Dataset on PO_Nbr and then simply subtract subquery total from PO_amt for each record.

Ian