Determining the overlap between payments and invoices

Given a table containing a list of invoices and a table containing a list of payments, I have the following problem: - Payments should be allocated to invoices up to the total amount of the invoice. - If the size of the payment is greater than the remaining amount on the invoice, the remainder should be allocated to the next invoice in line. - If there are no more invoices in line, the total amount of the invoice can be exceeded. All remaining payments can be allocated to the final invoice. For example, given the following tables: | InvoicedId | InvoiceAmount | | -------- | -------------- | | 1 | 5.00 | | 2 | 5.00 | | 3 | 5.00 | | PaymentId | PaymentAmount | | -------- | -------------- | | 1 | 4.00 | | 2 | 4.00 | | 3 | 4.00 | | 4 | 4.00 | The desired result would be the following: | InvoiceId | InvoiceAmount | PaymentId | PaymentAmount | | -------- | -------------- | -------- | -------------- | | 1 | 5.00 | 1 | 4.00 | | 1 | 5.00 | 2 | 1.00 | | 2 | 5.00 | 2 | 3.00 | | 2 | 5.00 | 3 | 2.00 | | 3 | 5.00 | 3 | 2.00 | | 3 | 5.00 | 4 | 4.00 | The best solution I was able to come up with involves loops and/or cursors, but my gut tells me there's a much better way to do it that I'm just not seeing.