hii all,
i have a follwing code , that produces the result given below;
with tb as (
select to_date('01/01/2010','dd/mm/yyyy') tdt, 'exn' tcode,1 tno,100 tdramt,0 tcramt from dual union all
select to_date('01/01/2010','dd/mm/yyyy'), 'exn',1, 0,100 from dual union all
select to_date('02/01/2010','dd/mm/yyyy'), 'exn',2, 0,100 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',3, 250,0 from dual union all
select to_date('03/01/2010','dd/mm/yyyy'), 'exn',4, 0,50 from dual union all
select to_date('04/01/2010','dd/mm/yyyy'), 'exn',4, 0,150 from dual
)
select tdt,tcode, tno, tdramt, tcramt,
sum(tdramt-tcramt) over (order by tdt,tcode,tno) ba from tb;
TDT TCO TNO TDRAMT TCRAMT BA
---------- --- ------------- ------------- ------------- -------------
01/01/2010 exn 1.000 100.000 .000 .000
01/01/2010 exn 1.000 .000 100.000 .000
02/01/2010 exn 2.000 .000 100.000 -100.000
03/01/2010 exn 3.000 250.000 .000 150.000
03/01/2010 exn 4.000 .000 50.000 100.000
04/01/2010 exn 4.000 .000 150.000 -50.000
6 rows selected.
but the expected is somthing lke this ;
TDT TCO TNO TDRAMT TCRAMT BA
---------- --- ------------- ------------- ------------- -------------
01/01/2010 exn 1.000 100.000 .000 100.000 <<== How to get this 100 in running sum,
01/01/2010 exn 1.000 .000 100.000 .000
02/01/2010 exn 2.000 .000 100.000 -100.000
03/01/2010 exn 3.000 250.000 .000 150.000
03/01/2010 exn 4.000 .000 50.000 100.000
04/01/2010 exn 4.000 .000 150.000 -50.000
6 rows selected.
the problem is when the tdt,tcode and tno are same..
how to take care of this situation like this...
kindly suggest..
TY