Re: Calc

On Wednesday 15 October 2003 12:59 pm, Muhyiddin A.M Hayat wrote:
> Dear all,
>
> I have below table
>
> +-----+--------------------+---------------------+
>
> | id | db |cr |
>
> +-----+--------------------+---------------------+
>
> |1 | | 200 |
> |2 | 100 | |
> |3 | 100 | |
> |4 | 150 | |
> |5 | | 200 |
>
> I Would like to calc that balance, and look like
>
> +-----+--------------------+---------------------+-----------+
>
> | id | db |cr |
> | bal |
>
> +-----+--------------------+---------------------+-----------+
>
> |1 | | 200
> | |200 | 2 | 100 |
> | |100 | 3 | 100 |
> | |0 | 4 | 150
> | | |-150 | 5 |
> | | 200 |50 |
>
> What can I do to get result like that

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.

Cheers,
Steve