On Mon, Nov 22, 2004 at 04:47:35PM +0000, Brett Parker wrote:
On Mon, Nov 22, 2004 at 04:17:56PM +0000, Chris Green wrote:
Yes, of course, I want a report that looks like:-
Date Description Amount Balance 01/09/04 Initial balance +1000.00 1000.00 03/09/04 Stamps -14.58 985.42 06/09/04 Network Card -9.99 975.43 12/09/04 Interest +0.10 975.53
date | description | amount | balance----------------------------+------------------+---------+--------- 2004-11-22 16:39:52.046068 | Starting Balance | 1000.00 | 1000.00 2004-11-22 16:40:11.58727 | Booze | -50.00 | 950.00 2004-11-22 16:40:30.539512 | Poker Winnings | 50.00 | 1000.00 2004-11-22 16:40:59.067902 | CD | -10.99 | 989.01
It's a little more complicated than the above but that shows the essentials. (I don't guarantee my arithmetic is correct)
The Date, Description and Amount columns are directly from a database table. The Balance column needs to ba calculated as the report is created.
Yup, that's how that's done... Here's the *MAGIC* ;)
create view testwithrunningtotal as select date,description,amount,(select sum(amount) from test as test2 where test2.date <= test.date) as balance from test order by date;
select * from testwithrunningtotal;
OK - so it's not pretty, but it does appear to work ;) Obviously, if you're not really using timestamps, like I was, and you can *assume* that your IDs are in the right order, then sort by those for the subselect.
If you need to be able to 'mess' with this, then you'll also need to set up some insert and delete rules on the view, probably ;)
Coo, I had sort of thought about an approach like that but hadn't really made the attempt. Thanks very much for pointing me in the right direction! That offloads yet another bit from Rekall.