[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