SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Payout (
    acctid VARCHAR(100),
	payseq INT,
	totalamount NUMERIC(19,2)
)

CREATE TABLE Account (
	acctnum VARCHAR(100),
	expectprice NUMERIC(19,2),
	payorder INT
)

INSERT Payout
VALUES
    ('aac1',  99, 1000.00),
    ('aac1', 100, 2000.00)

INSERT Account
VALUES
    ('1111aa',  800.00, 1),
    ('1111bb',  800.00, 2),
    ('1111cc',  800.00, 3),
    ('1111dd', 1200.00, 4)
6 rows affected

WITH CTE_Payout AS (
    SELECT *,
        SUM(totalamount) OVER(ORDER BY acctid, payseq) AS CummulativeTotalAmount,
        ISNULL(SUM(totalamount) OVER(
            ORDER BY acctid, payseq
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0) AS PriorCummulativeTotalAmount
    FROM Payout P
),
CTE_Acct AS (
    SELECT *,
        --SUM(expectprice) OVER(ORDER BY payorder) AS CummulativeExpectedPrice,
        ISNULL(SUM(expectprice) OVER(
            ORDER BY payorder
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
            ), 0) AS PriorCummulativeExpectedPrice
    FROM Account A
)
SELECT
    P.acctid, P.payseq, P.totalamount,
    A.acctnum, A.expectprice, A.payorder,
    LEAST(GREATEST(
            P.CummulativeTotalAmount - A.PriorCummulativeExpectedPrice,
        0), A.expectprice)
        - LEAST(GREATEST(
            P.PriorCummulativeTotalAmount - A.PriorCummulativeExpectedPrice,
        0), A.expectprice)
        AS paidamount,
    -- The following just show the working calculations
    P.CummulativeTotalAmount,
    LEAST(GREATEST(
            P.CummulativeTotalAmount - A.PriorCummulativeExpectedPrice,
        0), A.expectprice)
        AS CurrentCummulativeAllocation,
    P.PriorCummulativeTotalAmount,
    LEAST(GREATEST(
            P.PriorCummulativeTotalAmount - A.PriorCummulativeExpectedPrice,
        0), A.expectprice)
        AS PriorCummulativeAllocation
FROM CTE_Payout P
CROSS APPLY CTE_Acct A
ORDER BY acctid, payseq, payorder


acctid
payseq
totalamount
acctnum
expectprice
payorder
paidamount
CummulativeTotalAmount
CurrentCummulativeAllocation
PriorCummulativeTotalAmount
PriorCummulativeAllocation


aac1
99
1000.00
1111aa
800.00
1
800.00
1000.00
800.00
0.00
0.00

aac1
99
1000.00
1111bb
800.00
2
200.00
1000.00
200.00
0.00
0.00

aac1
99
1000.00
1111cc
800.00
3
0.00
1000.00
0.00
0.00
0.00

aac1
99
1000.00
1111dd
1200.00
4
0.00
1000.00
0.00
0.00
0.00

aac1
100
2000.00
1111aa
800.00
1
0.00
3000.00
800.00
1000.00
800.00

aac1
100
2000.00
1111bb
800.00
2
600.00
3000.00
800.00
1000.00
200.00

aac1
100
2000.00
1111cc
800.00
3
800.00
3000.00
800.00
1000.00
0.00

aac1
100
2000.00
1111dd
1200.00
4
600.00
3000.00
600.00
1000.00
0.00


-- WRONG ANSWER:
-- Does not deduct allocated amounts from running/remaining expectprice balance
SELECT
    *,
    GREATEST(LEAST(
            MAX(totalamount) OVER(PARTITION BY acctid, payseq)
            - SUM(expectprice) OVER(
                     PARTITION BY acctid, payseq
                     ORDER BY payorder
                     ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                ),
        expectprice), 0) AS paidamount
FROM Data
ORDER BY acctid, payseq, payorder
Msg 208 Level 16 State 1 Line 3
Invalid object name 'Data'.

acctid	payseq	totalamount	acctnum	expectprice	payorder	paidamount	CummulativeTotalAmount	CurrentCummulativeAllocation	PriorCummulativeTotalAmount	PriorCummulativeAllocation
aac1	99	1000.00	1111aa	800.00	1	800.00	1000.00	800.00	0.00	0.00
aac1	99	1000.00	1111bb	800.00	2	200.00	1000.00	200.00	0.00	0.00
aac1	99	1000.00	1111cc	800.00	3	0.00	1000.00	0.00	0.00	0.00
aac1	99	1000.00	1111dd	1200.00	4	0.00	1000.00	0.00	0.00	0.00
aac1	100	2000.00	1111aa	800.00	1	0.00	3000.00	800.00	1000.00	800.00
aac1	100	2000.00	1111bb	800.00	2	600.00	3000.00	800.00	1000.00	200.00
aac1	100	2000.00	1111cc	800.00	3	800.00	3000.00	800.00	1000.00	0.00
aac1	100	2000.00	1111dd	1200.00	4	600.00	3000.00	600.00	1000.00	0.00