SQL Sequence Order/Decrease Running Total

Transact-SQL
1

I'm trying to create a sql script/view that looks at a customers Cash Receipts and then identifies how the Payment should be applied to the customers oldest (by date) invoices. I'm having 2 issues with the below query: 1) if the customer has more than 1 Cash Receipt they are not sequentially being applied/reducing the invoices in order and 2) the apply amount doesn't seem to be correct when the customer has either 2 payments or 2 open invoices.

This is what I'm currently getting as a result set:

Customer_Nbr Apply_Date Source_Document_Nbr Check_Nbr Available_Apply_Amount Apply_to_Document_Nbr CURTRXAM DOCDATE Apply_Amount
120157 12/19/2018 OCC000000029131 6157 $27.16 120157-MIG-181031 $8.58 10/31/2018 $8.58
120157 12/19/2018 OCC000000029131 6157 $18.58 120157-MIG-181130 $13.96 11/30/2018 $18.58
120851 12/14/2018 PYMNT000000055963 EBX-000028375 $30.00 120851-MIG-180930 $5.60 9/30/2018 $5.60
120851 12/17/2018 PYMNT000000056319 EBX-000024444 $18.80 120851-MIG-180930 $5.60 9/30/2018 $18.80
120851 12/17/2018 PYMNT000000056319 EBX-000024444 $13.80 120851-MIG-181031 $17.65 10/31/2018 $13.80

Basically what I would want to happen is as follows:

Customer_Nbr Apply_Date Source_Document_Nbr Check_Nbr Available_Apply_Amount Apply_to_Document_Nbr CURTRXAM DOCDATE Apply_Amount
120157 12/19/2018 OCC000000029131 6157 $27.16 120157-MIG-181031 $8.58 10/31/2018 $8.58
120157 12/19/2018 OCC000000029131 6157 $18.58 120157-MIG-181130 $13.96 11/30/2018 $13.96
120851 12/14/2018 PYMNT000000055963 EBX-000028375 $30.00 120851-MIG-180930 $5.60 9/30/2018 $5.60
120851 12/14/2018 PYMNT000000055963 EBX-000028375 $24.40 120851-MIG-181031 $17.65 10/31/2018 $17.65

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (Partition by CR.CUSTNMBR order by INV.DOCDATE)
, CR.CUSTNMBR as Customer_Nbr, CR.DOCDATE as Apply_Date
, CR.DOCNUMBR as Source_Document_Nbr, CR.CHEKNMBR as Check_Nbr
, CR.CURTRXAM as Available_Apply_Amount
, INV.DOCNUMBR as Apply_to_Document_Nbr, INV.CURTRXAM
, INV.DOCDATE
, case when CR.CURTRXAM - sum(INV.CURTRXAM) OVER (Partition by CR.CUSTNMBR order by INV.DOCDATE, INV.DOCNUMBR) < 0 then 0 else CR.CURTRXAM - sum(INV.CURTRXAM) OVER (Partition by CR.CUSTNMBR order by INV.DOCDATE, INV.DOCNUMBR) end as AvailableBalance
, CR.BACHNUMB AS Batch_Id
From RM20101 CR (nolock)
JOIN RM20101 INV (nolock) on CR.CUSTNMBR = INV.CUSTNMBR --AND CR.CURNCYID = INV.CURNCYID
Where LEFT(INV.TRXSORCE,4) = 'SLST'
AND INV.RMDTYPAL = 1
AND INV.CURTRXAM <> 0
AND CR.CURTRXAM <> 0
AND CR.RMDTYPAL = 9

)
SELECT
CTE.Customer_Nbr, CTE.Apply_Date, CTE.Source_Document_Nbr, CTE.Check_Nbr,
CASE when prev.AvailableBalance is null then CTE.Available_Apply_Amount else prev.AvailableBalance end as Available_Apply_Amount,
CTE.Apply_to_Document_Nbr,
CTE.CURTRXAM,
CTE.DOCDATE
, CASE when prev.AvailableBalance is null then
CASE when CTE.Available_Apply_Amount < CTE.CURTRXAM then CTE.Available_Apply_Amount
ELSE CTE.CURTRXAM
END else prev.AvailableBalance
END as Apply_Amount
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1 AND CTE.Customer_Nbr = prev.Customer_Nbr
WHERE CASE WHEN prev.AvailableBalance is null then
CASE WHEN CTE.Available_Apply_Amount < CTE.CURTRXAM then CTE.Available_Apply_Amount
ELSE CTE.CURTRXAM
END ELSE prev.AvailableBalance END <> 0

Suggestions on order/etc.?

2

I first suggest you provide us usable sample data

create table #sample(customer_nbr int, apply_date date. source_document_nbr varchar(50), Available_Apply_Amount money)

insert into #sample
select 120157, '2018'12'19', 'OCC000000029131', 27.16 union
select 120157, '2018'12'19', 'OCC000000029131', 18.58