analytic function queries (HELP ME PLEASE)

[itanacious]

I've a table name [transaction]

idtransaction is my primary key

---------------- [transaction] -------------------------
idtransaction | customerID | amount | transDate

Amount is the credit or debit / they differ when amount < 0 it is credit and when amount > 0 it's debit

Sample Data
idtransaction | customerID | amount | transDate
1 | Cust001 | 200 | 12/03/07
2 | Cust001 | 200 | 12/04/07
3 | Cust001 | -100 | 12/13/07
4 | Cust001 | 500 | 12/23/07

I need to query like this: where customerID = cus001
balance = debit - credit , but for every row the it add the previous balance.


idtransaction | customerID | debit | credit | balance | transDate
1 | Cust001 | 200 | 0 | 200 | 12/03/07
2 | Cust001 | 200 | 0 | 400 | 12/04/07
3 | Cust001 | | 100 | 300 | 12/13/07

[amitpatel66]

I've a table name [transaction]

idtransaction is my primary key

---------------- [transaction] -------------------------
idtransaction | customerID | amount | transDate

Amount is the credit or debit / they differ when amount < 0 it is credit and when amount > 0 it's debit

Sample Data
idtransaction | customerID | amount | transDate
1 | Cust001 | 200 | 12/03/07
2 | Cust001 | 200 | 12/04/07
3 | Cust001 | -100 | 12/13/07
4 | Cust001 | 500 | 12/23/07

I need to query like this: where customerID = cus001
balance = debit - credit , but for every row the it add the previous balance.


idtransaction | customerID | debit | credit | balance | transDate
1 | Cust001 | 200 | 0 | 200 | 12/03/07
2 | Cust001 | 200 | 0 | 400 | 12/04/07
3 | Cust001 | | 100 | 300 | 12/13/07

This is how you do it in oracle:

 
SELECT idtransaction,customerid,SUM(amount) OVER(PARTITION BY customerid order by customerid ROWS BETWEEN 1 PRECEDING and CURRENT ROW) balance FROM transaction;