Re: Running Totals and other stuff....

The following is problem domain specific...

It appears you're actually after a primary key (cheque number springs to
mind), and are using OID to approximate this. In the absence of such a
key, I assume you're not interested in the sequence for a given date,
ie, two transactions for one day, for $5 and $10, you don't care if the
total is $5, $15, or $10, $15. If that's the case, then using OID is
fine within a day. So your SQL becomes

SELECT oid, *, (SELECT sum(amount) FROM checks WHERE x.thedate >=
thedate AND x.oid >= oid ) AS total
FROM checks x
ORDER BY thedate,oid ;

This keeps your totals unique, but will, as you say, change the sequence
within a day where cheques are inserted.

Regards

Alan Graham

BTW, troll follows. Please disregard.

Diatribes, rants, political statements are of no interest to most db
users. HTML mail is used all over the place. Deal with it. Keep posts
on topic.

Responses will go to /dev/null

Oh, and I top posted too.. :-0

On Tue, 2004-06-01 at 20:40, Levan, Jerry wrote:
> Humpfff...Last night I tried posting this and found that
> dynamic IP's are now prevented from posting to the list...
>
> Did I miss the announcement?
>
> ************************************
>
> 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
>
--
Alan Graham <alan(dot)graham(at)infonetsystems(dot)com(dot)au>