Re: [sqlite] More noobie questions

[Igor Tandetnik]

On 3/5/2013 12:06 AM, Kai Peters wrote:

given a table

ID           integer primary key,
PostDate  date,
Amount     int

with data

ID     PostDate        Amount
-----------------------------------------------------------
100             2013-01-01              -500
101             2013-01-02         1000
102        2013-01-03      200
103   2013-01-04    -600

I need to produce the following output if that's possible:

ID     PostDate        Amount    Withdrawal    Deposit   Balance
---------------------------------------------------------------------------
100             2013-01-01              -500                    500             
                             -500
101             2013-01-02         1000                               1000      
 500
102        2013-01-03      200                                  200       700
103    2013-01-04    -600                    600                    100

select ID, PostDate, Amount,
  (case when Amount < 0 then -Amount else null end) Withdrawal,
  (case when Amount >= 0 then Amount else null end) Deposit,
  (select sum(Amount) from MyTable t2 where t2.ID <= t.ID) Balance
from MyTable t order by ID;

--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users