Hi,
I keep all of my financial data in Postgresql ( 7.4.2).
My "Check" register records deposits, withdrawals (as amount) , date,
category and other stuff.
The following sorta works...
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >=
thedate ) AS total
FROM checks x
ORDER BY thedate,oid ;
The problem is that all transactions on the same date get the total of
all
transactions for that date, so the resulting table is only "sorta" a
running total.
If I change the rascal to look like
SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.oid >= oid ) AS
total
FROM checks x
ORDER BY thedate,oid ;
I get the right results, but this relies on the fact the oids in the
check table are currently *sorted* (when the table is sorted by thedate)
at least it appears that way via
a very brief inspection.... I suspect if I deleted a record and added
a record the oids would get out of sequence.
Is there a slick way to tell if a column (say the oids column) is in
"sorted"
order when the table is sorted by date?
Assuming the oids get out of wack with respect to the date, is it
possible
to easily construct a table of the checks sorted by date and then "glue
on"
a column of ascending integers so the running total sql statement will
function properly?
Jerry