Classic banking task-CodePudding

I think this is a usual task in banking area. I need to fill 'Income' column by previous values from 'Outcome'. But every 'Outcome' value calculated like Outcome = Income Debit - Credit from current row (each rows).

I guess I should use lag() for 'Income'. But this creates cyclicality in calculating.

I hope this can help:

create table account(acc_date date,income int, debit int, credit int, outcome int); insert into account values('2021-01-01', 100,800,500,400), ('2021-02-01', null,900,1500,null), ('2021-03-01', null,1700,2000,null), ('2021-04-01', null,2100,2800,null), ('2021-05-01', null,3500,4000,null); select * from account;

CodePudding user response：

Untested, but by using a sum() over() and coalesce in concert with the lag() over()

with cte as (
Select * 
      ,OutCome = sum( isnull(Income,0) Debit-Credit ) over (order by date)
 From YourTable
)
Select Date
      ,Income = coalesce(Income,lag(outcome,1) over (order by date))
      ,Credit
      ,Debit
      ,OutCome
 From  cte