I am trying to keep track if debits and credits, which I believe I got correct. In addition, I want to keep a running balance Total column as the last column but I can't seem to incorporate this into my solution.
Any help would be greatly appreciated. If possible, Below ie my test CASE. Any help would be greatly appreciated.
create table t(trans_id NUMBER, type varchar2(1),amt int,dt date);
insert into t values(1,'C',1000,to_date('10-Jul-2022','dd-mon-yyyy'));
insert into t values(2,'C',1200,to_date('10-Jul-2022','dd-mon-yyyy'));
insert into t values(3,'C',2000,to_date('11-Jul-2022','dd-mon-yyyy'));
insert into t values(4,'D',1000,to_date('12-Jul-2022','dd-mon-yyyy'));
insert into t values(5,'D',2000,to_date('12-Jul-2022','dd-mon-yyyy'));
insert into t values(6,'C',1100,to_date('12-Jul-2022','dd-mon-yyyy'));
select * from t
match_recognize(
order by dt
measures
dt dts ,
sum( decode(type,'C',amt,0)) credit_total,
sum( decode(type,'D',amt,0)) debit_total
one row per match
pattern( a b* )
define b as prev(dt) = dt);
DTS CREDIT_TOTAL DEBIT_TOTAL
10-JUL-22 2200 0
11-JUL-22 2000 0
12-JUL-22 1100 3000