SQL Server Partition by ID and Add or Subtract Preceding Row

Question

我试图计算基于开放余额和事件类型的ID的关闭余额

下面是样本数据

tbody> <<tr>131313103939394141475152525355

ID	Date_Opened	Opening_Amount	Event_Date	Event_Amount	Event_Remark
13	12/31/2019	74986	12/31/2019	74986	打开
	12/31/2019	74986	2/17/2020	74984	付款
	12/31/2019	74986	5/4/2020	74984	与
	12/31/2019	74986	7/19/2021	2	关闭
	9/10/2020	250000	9/10/2020	250000	打开
	5/26/2021	117075	5/7/2021	117075	打开
	5/26/2021	117075	7/14/2021	45467	付款
	5/26/2021	117075	8/17/2021	22083	付款
	6/3/2021	129096	5/26/2021	129096	打开
	6/3/2021	129096	8/13/2021	122772	付款
48	6/30/2021	42350	6/25/2021	42350	打开
48	6/30/2021	42350	8/3/2021	42196	付款
	7/23/2021	100980	6/24/2021	100980	打开
	7/28/2021	81458	7/15/2021	81458	打开
	7/30/2021	46750	7/27/2021	46750	打开
	7/30/2021	46750	8/24/2021	46750	付款
	8/6/2021	45100	8/2/2021	45100	打开
	8/11/2021	43725	8/9/2021	43725	打开

嗯…你应该能够使用窗口函数:

select t.*,
(case when Event_Remark = 'Open' then Event_Amount
when Event_Remark = 'Close' then 0
else sum(case when Event_Remark = 'Open' then event_amount else 0) over (partition by id order by date_opened) +
sum(case when Event_Remark = 'Reversal' then event_amount else 0) over (partition by id order by date_opened) -
sum(case when Event_Remark = 'Payment' then event_amount else 0) over (partition by id order by date_opened)
end) as closing_balance
from t;

注意:这里假设最多有一个"Open"one_answers";Close"每个帐户和"开放";是第一行和"close"。最后一行