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

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

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

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

CodePudding user response：

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