How much of each payment remains after partial refunds?

Define the decision and input records

Refunds are children of payments, so aggregate them by payment identity first. A pending request is not an actual cash return under the fixture’s status policy. The left join gives an unrefunded payment a zero refund amount, and the original payment remains visible for reconciliation.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE customers(id TEXT PRIMARY KEY, name TEXT);
CREATE TABLE invoices(id TEXT PRIMARY KEY, customer_id TEXT, amount_cents INTEGER, due_on TEXT, status TEXT);
CREATE TABLE payments(id TEXT PRIMARY KEY, invoice_id TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE refunds(id TEXT PRIMARY KEY, payment_id TEXT, amount_cents INTEGER, status TEXT);
INSERT INTO customers VALUES ('C1','Acorn'),('C2','Birch');
INSERT INTO invoices VALUES ('I1','C1',100000,'2026-10-01','issued'),('I2','C2',50000,'2026-10-20','issued'),('I3','C9',20000,'2026-10-02','issued'),('I4','C1',30000,'2026-09-01','void');
INSERT INTO payments VALUES ('P1','I1',40000,'settled'),('P2','I1',20000,'settled'),('P3','I2',50000,'settled'),('P4','I3',20000,'failed');
INSERT INTO refunds VALUES ('R1','P1',10000,'completed'),('R2','P1',5000,'pending'),('R3','P3',50000,'completed');

Run the query and compare the result

WITH r AS (
 SELECT payment_id,SUM(amount_cents) AS refunded_cents FROM refunds
 WHERE status='completed' GROUP BY payment_id
)
SELECT p.id,p.amount_cents,COALESCE(r.refunded_cents,0) AS refunded_cents,
 p.amount_cents-COALESCE(r.refunded_cents,0) AS retained_cents
FROM payments p LEFT JOIN r ON r.payment_id=p.id
WHERE p.status='settled' ORDER BY p.id;

P1 retains HK$300.00 after a completed HK$100.00 refund; its pending HK$50.00 request is excluded. P2 retains all HK$200.00. P3 retains zero after a full refund. These results distinguish a partial refund, no refund and a full refund without a single ambiguous refunded flag.

Apply the result within its limits

Negative retained amounts should trigger an over-refund investigation, not be silently capped at zero. The sample does not model chargebacks, provider fees, refund reversals or separate settlement dates.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.