sharing cash with priority to creditors in sql

Question

I have a table in sql 2014 with name "tblPaymentPlan" like this:

Creditors    PlanToPay      َAmount
----------------------------------
A            2017-01-20     2000
A            2017-02-20     1500
A            2017-03-20     3000
B            2017-01-25     3000
B            2017-02-25     1000

and also another table with name "tblPaid" like following:

Creditors    Paid      َ
-----------------
A            4500
B            3500

and the result that I expect:

Creditors    PlanToPay      َRemain
----------------------------------
A            2017-01-20     0
A            2017-02-20     0
A            2017-03-20     2000
B            2017-01-25     0
B            2017-02-25     500

I have no idea for doing this job at all! Would you please to help me to perform this job. Please informed that I have a lot of records in my tables. I need this query for budget planing. (We can use numbers for defining priority instead of dates)

Answer 1

You can subtract the running total from amount in paid table and if it is less than 0, set remain to 0 else the difference of amount from the running total.

select pp.creditors,pp.plantopay,
case when sum(pp.amount) over(partition by pp.creditors order by pp.plantopay)-coalesce(pd.paid,0) <= 0 then 0
else sum(pp.amount) over(partition by pp.creditors order by pp.plantopay)-coalesce(pd.paid,0) end as remain
from tblpaymentplan pp
left join tblPaid pd on pp.creditors=pd.creditors