Can pure SQL provide AR/AP reports?
Date: 20 Jun 2003 04:17:40 -0700
Message-ID: <8566aead.0306200317.39a6a6eb_at_posting.google.com>
I have 2 tables, owe and pay.
CREATE TABLE owe (
PRIMARY KEY (customer,DueDate),
customer CHAR(10),
DueDate DATE,
OweAmount INTEGER
);
CREATE TABLE pay (
PRIMARY KEY (customer,PayDate),
customer CHAR(10),
PayDate DATE,
PayAmount INTEGER
);
select * from owe order by DueDate;
customer | DueDate | OweAmount
------------+------------+----------- a | 2003-01-01 | 100 a | 2003-02-01 | 200 a | 2003-03-01 | 300 a | 2003-04-01 | 400 a | 2003-05-01 | 500 a | 2003-06-01 | 600
(6 rows)
select * from pay order by PayDate;
customer | PayDate | PayAmount
------------+------------+-----------a | 2003-01-01 | 90 a | 2003-02-05 | 160 a | 2003-02-27 | 250 a | 2003-02-28 | 100 a | 2003-04-02 | 450
(5 rows)
As we can see, this is a payment term where installment is allowed.
Table "owe" is designed to accomodiate unlimited number of customers
and due dates, so is table "pay".
Customer "a" is unlikely to make payment in time before deadlines.
I need a SQL to report the un-paid detail: customer | DueDate | OweAmount | PayAmount
------------+------------+-----------+----------- a | 2003-05-01 | 500 | 50 a | 2003-06-01 | 600 | 0
This report assumes that customers always make their payments to first
balance the oldest due amounts. That is said, it is NOT allowed for
customer "a" to:
pay $600 to balance the amount due on 2003-06-01 while refuse to pay
$100 due on 2003-01-01.
I am using PostgreSQL. Complicate SELECT clauses can be used.
Please help! TIA
CN
Received on Fri Jun 20 2003 - 13:17:40 CEST