On Sat, Nov 30, 2002 at 02:40:08PM -0800, Benjamin Smith wrote:
> Let's say you have a table of Financial transactions:
>
> Create table checks (
> id serial,
> number varchar,
> to varchar,
> amount real,
> date integer
> );
>
> (date is an epoch timestamp)
>
> And you want to get a listing of checks
>
> "SELECT * FROM checks ORDER BY date ASC";
>
> but you also want to have an accumulative field that adds up the amount field as the
>results are returned, so you might see results like:
>
> id number to amount date balance
> 1 0 Deposit -100 12344 100
> 2 100 Jack 40 123455 60
> 3 101 Bob 20 123345 40
> 4 102 VOID 0 0 40
> 5 103 Harold 11 123488 29
>
> Is this possible using only SQL?
>
> Also, assuming you have checks year round, how might you get results only in March
>that have totals consistent for the time frame while taking into account all the
>other checks in Jan and Feb?
create table checks (
id serial primary key,
num varchar unique,
"to" varchar,
amt real,
date date
);
insert into checks (num, "to", amt, date) values
(0,'deposit',100,'2002-01-01');
insert into checks (num, "to", amt, date) values
(0,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
(101,'jack',40,'2002-02-01');
insert into checks (num, "to", amt, date) values
(102,'bob',20,'2002-02-01');
insert into checks (num, "to", amt, date) values
(103,'VOID',0,'2002-02-01');
insert into checks (num, "to", amt, date) values
(104,'jenny',10,'2002-03-01');
insert into checks (num, "to", amt, date) values
(104,'raul',10,'2002-03-02');
insert into checks (num, "to", amt, date) values
(105,'raul',10,'2002-03-02');
select *,
( select sum(amt)
from checks c2
where c2.id<=c1.id as c2)
from checks c1;
will give you the full accounting. To get just March, put a
where-date-between clause in both the outer and inner queries.
This will run slowly, though, for many transactions. Either consider:
* "closing" an account every month/quarter/year/whenever will the
aggregate-so-far, and having your query use that, and do the math from
that point onwards
* store the running balance in the table, and use triggers to keep it up
to date for inserts/updates/deletes
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
