Matching up invoice amounts
Versions:
Informix c4gl 7.32.UC4
Informix SE 7.25.UC6R1
I'm writing a 4GL program that will match up invoice amounts so that
only unpaid invoices are displayed. Below is my thought on how this
should be done. I want to make sure there isn't a better way that I
should be doing this. For example instead of doing a select sum()
should I use a cursor and put some code in a foreach to match the
invoice and payment amounts? There will typically be about 30 rows
that I'll be working with.
SA = Sales Invoice
PY = Payment
drop table t1;
drop table t2;
create temp table t1 (tran_type char(2),
amount decimal(10,2),
date date,
invoice_nbr integer);
insert into t1 values("SA", 100, "010109", 1111);
insert into t1 values("PY", -100, "013109", 1111);
insert into t1 values("SA", 152, "010209", 2222);
insert into t1 values("PY", -100, "011009", 2222);
insert into t1 values("SA", 500, "020109", 3333);
select invoice_nbr, sum(amount) amount
from t1
group by 1
having sum(amount) != 0
into temp t2;
select distinct t1.invoice_nbr,
min(t1.date) date,
t2.amount
from t1, t2
where t1.invoice_nbr = t2.invoice_nbr
group by 1,3
invoice_nbr date amount
2222 01/02/2009 52.00
3333 02/01/2009 500.00
Invoice 1111 has been completely paid and invoice 2222 has a balance
of 52.00 and 3333 has a balance of 500.00.