for my subjet i am making use 3 tables.
SQL> select * from op_bal;
OP_COD OP_NAME OP_DATE OP_AMOUNT
------ ------------------ --------- -------------
OP0000 NATIONAL BANK 01-JAN-09 5000.000
SQL> desc EXPENSE_MASTER
Name Null? Type
------------ -------- ------------
EXPN_CODE VARCHAR2(6)
EXPN_NAME VARCHAR2(30)
EXPN_EI VARCHAR2(1)
SELECT OP_DATE,NULL TNO,OP_CODE,NULL EI,NULL WITHDRAWAL,OP_AMOUNT DEPOSIT
FROM OP_BAL
UNION ALL
SELECT
EXN_DATE,
EXN_NO,
EXN_ACNT_CODE,
EXPN_EI,
DECODE(EXPN_EI,'E',EXN_AMOUNT) WITHDRAWAL,
DECODE(EXPN_EI,'I',EXN_AMOUNT) DEPOSIT
FROM EXPENSES_TXN, EXPENSE_MASTER
WHERE EXN_ACNT_CODE = EXPN_CODE
order by 1;
OP_DATE TNO OP_COD E WITHDRAWAL DEPOSIT
--------- ------------- ------ - ------------- -------------
01-JAN-09 OP0000 5000.000
01-MAR-09 2.000 AC0002 E 2000.000
10-MAR-09 7.000 AC0012 I 500.000
15-MAR-09 5.000 AC0007 E 15.000
20-MAR-09 8.000 AC0012 I 700.000
31-MAR-09 6.000 AC0008 E 30.000
01-APR-09 9.000 AC0013 I 250.000
07-APR-09 1.000 AC0001 E 200.000
09-APR-09 3.000 AC0003 E 50.000
09-APR-09 4.000 AC0011 E 35.000
10 rows selected.
I am desgining a query that should give me the following result.
OP_DATE TNO OP_COD E WITHDRAWAL DEPOSIT BAL
--------- ------------- ------ - ------------- ------------- -------------
01-APR-09 OP0000 2045.000 6200.000 4155.000
01-APR-09 9.000 AC0013 I 250.000 4405.000
07-APR-09 1.000 AC0001 E 200.000 4205.000
09-APR-09 4.000 AC0011 E 35.000 4170.000
09-APR-09 3.000 AC0003 E 50.000 4120.000
to get this i wrote this query, but got wrong result;
SQL> SELECT OP_DATE,TNO,OP_CODE,EI,WITHDRAWAL,DEPOSIT,
2 sum(NVL(DEPOSIT,0)-NVL(WITHDRAWAL,0)) over (partition by op_date order by op_date) bal
3 FROM (SELECT OP_DATE,NULL TNO,OP_CODE,NULL EI,NULL WITHDRAWAL,OP_AMOUNT DEPOSIT
4 FROM OP_BAL
5 UNION ALL
6 SELECT
7 EXN_DATE,
8 EXN_NO,
9 EXN_ACNT_CODE,
10 EXPN_EI,
11 DECODE(EXPN_EI,'E',EXN_AMOUNT) WITHDRAWAL,
12 DECODE(EXPN_EI,'I',EXN_AMOUNT) DEPOSIT
13 FROM EXPENSES_TXN, EXPENSE_MASTER
14 WHERE EXN_ACNT_CODE = EXPN_CODE
15 order by 1) a
16 WHERE OP_DATE between to_date('01/04/2009','dd/mm/yyyy') and to_date('30/4/2009','dd/mm/yyyy');
OP_DATE TNO OP_COD E WITHDRAWAL DEPOSIT BAL
--------- ------------- ------ - ------------- ------------- -------------
01-APR-09 9.000 AC0013 I 250.000 250.000
07-APR-09 1.000 AC0001 E 200.000 -200.000
09-APR-09 4.000 AC0011 E 35.000 -85.000
09-APR-09 3.000 AC0003 E 50.000 -85.000
i want a query that will always go back from the
FROM DATE in where condition (means before 01/04/2009) and
SUMM records prior to 01/04/2009, PLUS ADDING the opening total from the OP_BAL table and display as first Record.
and thereafter it should go on with the cumulative sum.
your timely help will be highlt appreciated as my badly stuck