>我有给定格式的数据：
Id | Total_Inv_Amount | Total_Rec_Inv_Amount | Invoice_No Invoice_Amont | Payment_Outstanding | Refund_Outstanding
1 | 25000 | 22000 | 5 | 15000 | 0 | 0
1 | 25000 | 22000 | 6 | 10000 | 0 | 0
2 | 45000 | 48000 | 10| 25000 | 0 | 0
2 | 45000 | 48000 | 15| 20000 | 0 | 0
预期结果....
Id | Total_Inv_Amount | Total_Rec_Inv_Amount | Invoice_No Invoice_Amont | Payment_Outstanding | Refund_Outstanding
1 | 25000 | 22000 | 5 | 15000 | 0 | 0
1 | 25000 | 22000 | 6 | 10000 | 3000 | 0
2 | 45000 | 48000 | 10| 25000 | 0 | 0
2 | 45000 | 48000 | 15| 20000 | 0 | 2000
计算 ：--
发票编号 5 & 6 总金额为：15000+10000 = 25000 收到的总金额为：22000现在在发票编号的字段 Payment_Outstanding 中
为 0 total_invoice_amt>因为发票 5 金额 22000>15000 比未付款项为 0 剩余金额为 22000-15000 = 7000现在从下一张发票中扣除此金额，金额为 10000
10000-7000 = 3000 这是未付款项
现在请帮助我如何在查询中计算它
递归查询非常适合获取运行值。 第一个 cte 添加一个行号，该行号由递归部分使用。在递归部分完成计算。在最终结果中，不会显示低于 0 的值。
Declare @myTable table (Id int, Total_Inv_Amount float,
Total_Rec_Inv_Amount float,Invoice_No int,
Invoice_Amount float, Payment_Outstanding float,
Refund_Outstanding float)
insert into @myTable values
(1 , 25000 , 22000 , 5 , 15000 , 0 , 0 ),
(1 , 25000 , 22000 , 6 , 10000 , 0 , 0 ),
(2 , 45000 , 48000 , 10, 25000 , 0 , 0 ),
(2 , 45000 , 48000 , 15, 20000 , 0 , 0 )
;with first_cte as
(
select *, ROW_NUMBER() over (partition by id order by invoice_no) rn
from @myTable
)
, result_rte as
(
select m.Id, m.Total_Inv_Amount,m.Total_Rec_Inv_Amount,
m.Invoice_No, m.Invoice_Amount, m.Payment_Outstanding,
m.Refund_Outstanding, m.rn, invoice_Amount TotalAmount
from first_cte m
where rn = 1
union all
select m.Id, m.Total_Inv_Amount,m.Total_Rec_Inv_Amount,
m.Invoice_No, m.Invoice_Amount,
r.TotalAmount + m.Invoice_Amount - m.Total_Rec_Inv_Amount ,
m.Total_Rec_Inv_Amount - (r.TotalAmount + m.Invoice_Amount),
m.rn, r.TotalAmount + m.Invoice_Amount
from result_rte r
join first_cte m on r.id = m.id and r.rn+1 = m.rn
)
select Id, Total_Inv_Amount,Total_Rec_Inv_Amount,Invoice_No, Invoice_Amount,
case when Payment_Outstanding > 0 then Payment_Outstanding else 0 end Payment_Outstanding ,
case when Refund_Outstanding > 0 then Refund_Outstanding else 0 end Refund_Outstanding
from result_rte order by invoice_no