SQL-按上升顺序从行减去付款金额？

发布于 2025-02-02 02:29:56 字数 2130 浏览 5 评论 0原文

我有一张付款方式，看起来像下面，其中包含有关合同的信息，以及我们期望获得的付款。

ContractKey	付款	总计	00
385884	前期	95.356	2022-05-17 00：00：00.000
385884	首先	1	2022-06-09 00：00.00.000
385884	最终	143.034	2024-07-17-17-17：00：00：00：00：00：00：00：00：00：00：00：00：00：00：00：

;按照以下结构的合同级别收到的付款。PaymentkeyContractkey

总计	47.68	1
385884	。	这些

表使用ContractKey加入我要做的是在我的付款方式表中添加一列，该表显示了已经在付款表中还清的每个计划付款的金额。因此，下面的示例我们可以看到ContractKey 385884已收到47.68，然后在我的计算列中显示下面的示例。

我已经写了下面的SQL，它没有给我。正确输出后续行。

with debitdetails as(
select contractkey,sum(total)[totalpaid]  from fact.Payments

group by contractkey
)
select s.contractkey, s.Payment, s.total, [DueDate],
sum(s.total) over (partition by s.contractkey order by [DueDate] asc) - totalpaid [TotalRemaining]
from [ref].[PaymentSchedule] s
left join debitdetails dd on s.contractkey=dd.ContractKey
where s.contractkey = 385884
order by s.contractkey

这给了我以下..这不是我想要的，因为我希望它向我展示所需的金额，剩余的金额剩下多少。 So the 2nd row should show as 1, and the third as 143.03

contractkey	Payment	total	DueDate	TotalRemaining
385884	Upfront	95.356	2022-05-17 00:00:00.000	47.676
385884	First	1	2022-06-09 00:00:00.000	47.676
385884	Final	143.034	2024-07-17 00：00：00.000	190.71

任何人可以帮助我确定我出错的地方吗？我认为我只是错过了真正简单的东西。

原文

I have a PaymentSchedule table that looks like the below which contains information about contracts, and when we expect to get paid on them.

contractkey	payment	total	DueDate
385884	Upfront	95.356	2022-05-17 00:00:00.000
385884	First	1	2022-06-09 00:00:00.000
385884	Final	143.034	2024-07-17 00:00:00.000

I then have another table which contains payments received at ContractKey level structured like the below..

PaymentKey	ContractKey	Total
1	385884	47.68

These tables are joined using ContractKey. What I am trying to do is add a column to my PaymentSchedule table which shows the amount of each scheduled payment that has already been paid off in the Payments table. So the example below we can see that 47.68 has been received for ContractKey 385884, which should then show in my calculated column the below..

I have wrote the below SQL and it isn't giving me the correct output for the subsequent rows..

with debitdetails as(
select contractkey,sum(total)[totalpaid]  from fact.Payments

group by contractkey
)
select s.contractkey, s.Payment, s.total, [DueDate],
sum(s.total) over (partition by s.contractkey order by [DueDate] asc) - totalpaid [TotalRemaining]
from [ref].[PaymentSchedule] s
left join debitdetails dd on s.contractkey=dd.ContractKey
where s.contractkey = 385884
order by s.contractkey

This is giving me the below.. which isn't what I want as I want it to show me of the amount due, how much is remaining after minusing the already paid amount. So the 2nd row should show as 1, and the third as 143.03

contractkey	Payment	total	DueDate	TotalRemaining
385884	Upfront	95.356	2022-05-17 00:00:00.000	47.676
385884	First	1	2022-06-09 00:00:00.000	47.676
385884	Final	143.034	2024-07-17 00:00:00.000	190.71

Can anyone help me identify where I am going wrong? I assume I am just missing something really simple..

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

病毒体 2025-02-09 02:29:56

用例表达式检查total paid针对累积总和，并计算剩余数量相应的

第一个条件是当Total Paid超过累积总和时，剩余的= 0

秒条件是当Total Propaid只能部分地付款时覆盖累计总和

最终条件（else）是当总薪酬完全不足以支付金额时，剩余= 0

TotalRemaining = case when isnull(dd.totalpaid, 0) 
                      >=   sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                      then 0
                      when isnull(dd.totalpaid, 0)
                      >=   sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                           - s.Total
                      then sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                      -    isnull(dd.totalpaid, 0)
                      else s.Total
                      end

use case expression to check the totalpaid against the cumulative sum and calculate the remaining amount accordingly

First condition is when totalpaid is more than the cumulative sum, so remaining = 0

Second condition is when totalpaid is only able to partially cover the cumulative sum

Final condition (else) is when totalpaid is totally not enough to cover amount, so Remaining = 0

TotalRemaining = case when isnull(dd.totalpaid, 0) 
                      >=   sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                      then 0
                      when isnull(dd.totalpaid, 0)
                      >=   sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                           - s.Total
                      then sum(s.Total) over (partition by s.contractkey
                                                  order by s.DueDate)
                      -    isnull(dd.totalpaid, 0)
                      else s.Total
                      end

回复收藏 0 原文

~没有更多了~