ORACLE SQL - sum based on previous row data

Question

I have a table that looks like this:

tran_date	tran_num	tran_type	tran_status	amount
2/12/2021	74	SG	N	1461
9/1/2021	357	SG4	N	2058
10/27/2021	46	SG4	Y	-2058
10/27/2021	47	SG	N	2058

I would like to identify rows that have negative amounts and sum to previous row to 0, if ordered by tran_date and tran_num.

Expected output:

tran_date	tran_num	tran_type	tran_status	amount
2/12/2021	74	SG	N	1461
10/27/2021	46	SG4	Y	0
10/27/2021	47	SG	N	2058

Answer 1

You can try using a lag function in sub-query and then calculating the amount like this:

select x.tran_date,x.tran_num, x.tran_type, x.tran_status,
case 
    when x.amount<0 then x.amount + x.lag_win
    else x.amount
end amount
from 
(
select tt.*, 
       lag(amount,1) over (order by tran_date,tran_num) lag_win,
       abs(tt.amount)+lead (amount,1,1) over (order by tran_date,tran_num) pseudo_col
from tran_table tt
)x
where pseudo_col!=0

RESULT:

12.02.2021  74  SG  N   1461
27.10.2021  46  SG4 Y   0
27.10.2021  47  SG  N   2058