Loop through one table’s debit amount to find another tables matching credit amount

I have a table of accounts receivable transactions where I need to allocate payments to invoices automatically. I am battling to come up with a SQL script that will match the debits in one column to the credits in another column. I was thinking of making two temp tables from the transactions table, one for debits and one for credits but I’m not sure how to loop through each row in the tables to find the matching debit-credit allocation and insert it into an allocations table

For example:

Transactions

ID	AccountLink	Date	Reference	Descr	Debit	Credit
3	CUS001	2012/01/03	INV001	Invoice to Customer 1	3000.00	0
4	CUS004	2012/01/04	INV002	Invoice to Customer 4	2500.00	0
5	CUS001	2012/01/06	PMT001	Customer Payment	0	3000.00

Temp_Debits

ID	Reference	Amount
3	INV001	3000.00

Temp_Credits

ID	Reference	Amount
5	PMT001	3000.00

Allocations

From ID	To ID	Amount
5	3	3000

**The transactions would be matched with a specific given account link, the inv-payment link have to be from that same account link and the matching transactions have to be within a 3 month period of each other

As stated by @Fabio, To link payments to invoices most of the systems using invoice reference where payee is required to provide same reference with the payment. Payment without reference always goes to manual matching. In this case any payment can match to any invoice as long as its in the same 3 month period.

Answer

You don’t need to loop. What you’re saying is “find all records for the same account where the debit in one row matches the credit in the other row, within 3 months of eachother.” You can do that via a self join.

As @GordonLinoff says, if you have more than one row that matches the criteria, you’ll get multiple results; without more ways to distinguish the results, you’ll need a human to figure it out. You will also not pick up invoices that were paid in more than one payment.

select c.ID as from_id, 
d.ID as to_id, 
c.credit as amount
from transactions c
inner join transactions d
where c.credit = d.debit
and   c.credit > 0
and   c.id <> d.id
and   c.accountLink = d.accountLInk
and   datediff('mm', c.date, d.date) <= 3