Re: Can pure SQL provide AR/AP reports?

"CN" <cnliou9_at_fastmail.fm> wrote in message news: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

The following works under MS SQL Server and is completely Standard SQL. Hopefully you have similar success under PostgreSQL. By the way, thanks for the DDL and sample data.

SELECT O.customer,

               O.duedate,
               O.oweamount,
               CASE WHEN PaidTotal.total >= OwedToDate.prevtotal
                          THEN PaidTotal.total - OwedToDate.prevtotal
                          ELSE 0
               END AS payamount
FROM (SELECT C.customer, COALESCE(SUM(P.payamount), 0)
              FROM (SELECT DISTINCT customer
                           FROM Owe) AS C(customer)
                          LEFT OUTER JOIN
                          Pay AS P
                          ON C.customer = P.customer
              GROUP BY C.customer) AS PaidTotal(customer, total)
            INNER JOIN
            (SELECT O1.customer,
                             O1.duedate,
                             SUM(O2.oweamount),
                             SUM(O2.oweamount) - O1.oweamount
             FROM Owe AS O1
                         INNER JOIN
                         Owe AS O2
                         ON O1.customer = O2.customer AND
                                O2.duedate <= O1.duedate
             GROUP BY O1.customer, O1.duedate, O1.oweamount)
            AS OwedToDate(customer, duedate, total, prevtotal)
            ON OwedToDate.customer = PaidTotal.customer
            INNER JOIN
            Owe AS O
            ON O.customer = OwedToDate.customer AND
                   O.customer = PaidTotal.customer AND
                   O.duedate = OwedToDate.duedate AND
                   PaidTotal.total < OwedToDate.total

Note that this solution works even if no payments have been received from a customer but yet money is owed (imagine that!). For example, if customer "a" owes as described above but has made no payments, that is, there are no entries for this customer in the Pay table, the result is

customer duedate oweamount payamount

a          2003-01-01 00:00:00.000 100 0
a          2003-02-01 00:00:00.000 200 0
a          2003-03-01 00:00:00.000 300 0
a          2003-04-01 00:00:00.000 400 0
a          2003-05-01 00:00:00.000 500 0
a          2003-06-01 00:00:00.000 600 0

Regards,
jag Received on Fri Jun 20 2003 - 23:21:17 CEST