Works for me! :o)
Regards,
Keith ____________ Sometimes the highest form of action is inaction. - Jerry Brown
-----Original Message----- From: main-admin@lists.alug.org.uk [mailto:main-admin@lists.alug.org.uk]On Behalf Of Brett Parker Sent: 22 November 2004 16:48 To: main@lists.alug.org.uk Subject: Re: [ALUG] Re: Running total from a database table - how?
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 ;)
Cheers,
Brett Parker web: http://www.sommitrealweird.co.uk/ email: iDunno@sommitrealweird.co.uk
main@lists.alug.org.uk http://www.alug.org.uk/ http://lists.alug.org.uk/mailman/listinfo/main Unsubscribe? See message headers or the web site above!
-
Keith Watson