On Thursday 16 October 2003 10:37 am, Muhyiddin A.M Hayat wrote:
> > Something like this:
> >
> > select id,db,cr,(select sum(cr-db) from calc sub where sub.id <=
> > calc.id) from calc;
> >
> > This of course assumes that ID indicates the correct order of the
> > entries and it will blow up if you allow nulls for cr or db
> > (which you shouldn't since that would literally be interpreted as
> > "they withdrew 10 and deposited an unknown amount"). If you have
> > null values already and they should be interpreted as 0 just do
> > this:
> >
> > select id, db, cr, (select sum(coalesce(cr,0)-coalesce(db,0))
> > from calc sub where sub.id <= calc.id) from calc;
> >
> > I assume no responsibility for potential lack of scalability of
> > this query. :) It's quite possible a faster solution exists -
> > we'll see what comes up on the list.
>
> If data from "View" without ID, how can I do?
>
> My View:
>
> trx_date | trx_time | descriptions |
> payment_method | debet | credit | creator
> ------------+----------+--------------------------------------+----
>--------- ---+-------+----------+---------
> 2003-10-09 | 21:55:02 | Resto Biling : 13,800, Paid : 10,000 |
> Visa
>
> | 3800 | | middink
>
> 2003-10-16 | 03:28:30 | Payment - Thank You |
> Visa
>
> | | 40000.00 | middink
>
> 2003-10-08 | 18:17:40 | Payment - Thank You |
> Cash
>
> | | 50000.00
The id field only keeps the transactions in the correct order so you
can sum the previous transactions. You can do the same thing with
your date and time fields (though using a single datetime field would
be easier). A caution, though: you will have trouble if two
transactions share the same date and time.
Cheers,
Steve