I have 2 tables, outstanding and payment.
Outstanding table contains
| OutstandingID | OutstandingAmount |
|---|---|
| 1 | 1000 |
| 2 | 500 |
| 3 | 2000 |
Payment table contains
| PaymentID | PaymentAmount |
|---|---|
| 1 | 500 |
| 2 | 1000 |
| 3 | 1500 |
| 4 | 500 |
How to join the two tables to create a distribution table indicating which outstanding ID is paid by which payment ID and by what amount.
Expected distribution
| OutstandingID | PaymentID | PaymentAmount |
|---|---|---|
| 1 | 1 | 500 |
| 1 | 2 | 500 |
| 2 | 2 | 500 |
| 3 | 3 | 1500 |
| 3 | 4 | 500 |
I can easily achieve this result using a simple loop and calculate the payment balance for each outstanding. But I’m afraid it will be slow for large data. So is there any PostgreSQL query that can calculate this in batch without looping?