[thelist] Invoices and ledgers and schema

Bill Moseley moseley at hank.org
Mon Jun 5 23:11:50 CDT 2006


On Mon, Jun 05, 2006 at 09:44:48PM -0600, Ken Moore wrote:
> Hi Bill,
> 
> I'm not certain that I understand, but applying standard database concepts, 
> this is what I would say.
> 
> The lefger table needs 1 change. Remove the amount field (column) as 
> follows.
[...]
> 
> Ditto for the balance field in invoice table.

Yep, that's exactly what I did.  Posting to a list often makes things
so much clearer. ;)

I was getting stuck on calculating "previous balance" -- I ended up
doing it by invoice date.  I think that's reasonable.


(cart_transaction is the ledger table).

    SELECT
        sum(amount) as amount_due,
        sum(CASE WHEN i.invoice_time < cutoff.invoice_time THEN amount ELSE 0 END)
            as prev_balance
    FROM
        cart_transaction t
        JOIN invoice i ON t.invoice = i.id
        JOIN (select invoice_time, cart from invoice where id = 3) as cutoff
            ON t.cart = cutoff.cart
    WHERE
        i.invoice_time <= cutoff.invoice_time;



-- 
Bill Moseley
moseley at hank.org




More information about the thelist mailing list