CREATE TABLE transactions (
ID INT,
amount INT,
document_id INT
);
INSERT INTO transactions VALUES
(1, 100.00, 11),
(2, 80.00, 12),
(3, 120.00, 13);
CREATE TABLE bill_details (
ID INT,
amount INT,
document_id INT,
description VARCHAR(64)
);
INSERT INTO bill_details VALUES
(1, 20.00, 11, 'A'),
(2, 60.00, 11, 'B'),
(3, 20.00, 11, 'C'),
(4, 80.00, 12, 'D'),
(5, 60.00, 13, 'E'),
(6, 20.00, 13, 'F'),
(7, 20.00, 13, 'G'),
(8, 40.00, 13, 'H');
SELECT
description,
/*bill_details.amount,
bill_details.document_id,
SUM(bill_details.amount) OVER (PARTITION BY document_id ORDER BY bill_details.ID) S,
transactions.amount,*/
bill_details.amount + least(
transactions.amount -
SUM(bill_details.amount) OVER (PARTITION BY document_id ORDER BY bill_details.ID),
0
) amount
FROM bill_details
JOIN transactions USING(document_id)
ORDER BY bill_details.ID