经典银行业任务

Question

我认为这是银行界的一项常见任务。我需要填写'Income'栏中的'Outcome'的先前值。但是，计算出的每个“结果”值都类似于Outcome = Income + Debit - Credit。

我想我应该用的“收入”()来表示滞后()。但这在计算中创造了周期性。

我希望这能帮助：

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;

Answer 1

未经测试，但使用sum() over()并与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