I have a table that looks like this:

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:

CodePudding user response：

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