#oracle11gr2

[lovemounika14blog-blog]

Can a data block accommodate rows of distinct tables?

[bitecode]

SQL For deriving Running Balance/Total

Create Table as Follows

create table TRAN ( ACID VARCHAR2(11), TRAN_DATE VARCHAR2(20), TRAN_TYPE VARCHAR2(1), TRAN_AMT NUMBER(12,4), TRAN_PARTICULAR VARCHAR2(50) );

Insert The Following Values

insert into TRAN values ('000001', to_date('15-03-2014', 'dd-mm-yyyy'), 'C', 100, 'CREDIT'); insert into TRAN values ('000001', to_date('16-03-2014', 'dd-mm-yyyy'), 'D', 100, 'DEBIT'); insert into TRAN values ('000001', to_date('17-03-2014', 'dd-mm-yyyy'), 'D', 300, 'DEBIT'); insert into TRAN values ('000002', to_date('14-03-2014', 'dd-mm-yyyy'), 'C', 100, 'CREDIT'); insert into TRAN values ('000002', to_date('15-03-2014', 'dd-mm-yyyy'), 'C', 200, 'CREDIT'); insert into TRAN values ('000002', to_date('16-03-2014', 'dd-mm-yyyy'), 'D', 300, 'DEBIT');

This is how the table looks (Select All Values)

SELECT * FROM TRAN; ACID TRAN_DATE TRAN_TYPE TRAN_AMT PARTICULAR 000001 15-03-2014 C 100.00 CREDIT 000001 16-03-2014 D 100.00 DEBIT 000001 17-03-2014 D 300.00 DEBIT 000002 14-03-2014 C 100.00 CREDIT 000002 15-03-2014 C 200.00 CREDIT 000002 16-03-2014 D 300.00 DEBIT

Calculate Running Total

SELECT B.ACID, B.TRAN_DATE, B.TRAN_PARTICULAR as PARTICULARS, CASE WHEN B.TRAN_TYPE = 'D' THEN B.TRAN_AMT END AS DEBIT, CASE WHEN B.TRAN_TYPE = 'C' THEN B.TRAN_AMT END AS CREDIT, SUM(CASE WHEN B.TRAN_TYPE = 'D' THEN B.TRAN_AMT WHEN B.TRAN_TYPE = 'C' THEN -B.TRAN_AMT ELSE 0 END) OVER(PARTITION BY B.ACID ORDER BY B.TRAN_DATE) AS BALANCE FROM TRAN B

The above Query return's the following result

ACID TRAN_DATE PARTICULARS DEBIT CREDIT BALANCE 000001 15-03-2014 CREDIT 100.00 -100.00 000001 16-03-2014 DEBIT 100.00 0.00 000001 17-03-2014 DEBIT 300.00 300.00 000002 14-03-2014 CREDIT 100.00 -100.00 000002 15-03-2014 CREDIT 200.00 -300.00 000002 16-03-2014 DEBIT 300.00 0.00

Further Reading & Credits...

asktom