running total with out of sequence key

From: Jon Griffin <nospam_at_e88.org>
Date: Tue, 24 Oct 2000 05:41:28 GMT
Message-ID: <ci9J5.21785$og.1698609_at_typhoon.we.rr.com>

the following SQL works fine except when you insert a transaction that is earlier than the last transaction. Taking out the where clause in the sub-select gives the right balance (in the end) but doesn't work when 2 transactions are on the same day (as Joe Celko describes in his book). I know I am missing the obvious but...

---

select

    cash_log_id,
    to_char(c0.transaction_date,'MM/DD/YY') as transaction_date,

    c0.party_id,
    c0.description,
    c0.category,

    to_char(c0.transaction_amount,'999,999,999.99') as pretty_transaction_amoun\
t,

    c0.transaction_amount,
    c0.account_type,
    to_char ((select sum(c1.transaction_amount)

        from cash_logs c1
        where c1.transaction_date <= c0.transaction_date
        and c1.party_id = :user_id
        and c1.cash_log_id <= c0.cash_log_id
        ) ,'999,999,999.99') as balance

from cash_log_end_user_view c0
where c0.party_id = :user_id
and c0.category = cat.category_id
and c0.account_type = a.account_type_id
order by transaction_date desc Received on Tue Oct 24 2000 - 07:41:28 CEST