I am trying to work through this problem and need help.
The data looks like this:
ROID TransactionType Amount
1 Invoice 1500
1 Payment - Undeposited Funds 1400
1 Payment - Undeposited Funds 500
2 Invoice 2000
2 Payment - Undeposited Funds 1500
2 Payment - Undeposited Funds 500
The results I am looking for is just to see ROID 1 with the amount of 400 which is the total of the multiple payments less the amount of the invoice. In this case they overpaid the invoice by $400.
I was thinking of a union query but I am not getting the results I am expecting. This is what I have so far:
sum(amount) AS amount
ledger.transactiontype = 'Invoice'
Sum(ledger.amount) * - 1 AS amount
ledger.transactiontype = 'Payment - Undeposited Funds'
Any thoughts, alternatives, etc is appreciated.