add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE installment (
acc_name VARCHAR(25),
installment_amt INT,
installment_no INT,
paid_amt INT);

INSERT INTO installment VALUES
('A',100,1,100),
('A',100,2,90),
('A',100,3,80),
('A',100,4,0),
('A',100,5,0),
('B',100,1,100),
('B',100,2,90),
('B',100,3,5),
('B',100,4,0),
('B',100,5,0);
SELECT acc_name,
installment_amt,
installment_no,
paid_amt,
CASE WHEN paid_amt > 0
AND paid_amt+nxp >= installment_amt
AND diff > 0
THEN 0
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND nxp > 0
THEN nxp
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND LAG(diff) OVER (PARTITION BY acc_name ORDER BY installment_no) > 0
THEN LAG(installment_amt-diff) OVER (PARTITION BY acc_name ORDER BY installment_no)
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND diff <0
THEN ABS(diff)+paid_amt
ELSE 0
END balance
FROM
(SELECT *,
CASE WHEN paid_amt > 0
AND paid_amt+nxp >= installment_amt
THEN paid_amt+nxp-installment_amt
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
THEN (paid_amt+nxp)-installment_amt
END AS diff
FROM
(SELECT *,
CASE WHEN paid_amt < installment_amt THEN
LEAD(paid_amt) OVER (PARTITION BY acc_name ORDER BY installment_no)
ELSE 0 END nxp
acc_name installment_amt installment_no paid_amt balance
A 100 1 100 0
A 100 2 90 0
A 100 3 80 30
A 100 4 0 0
A 100 5 0 0
B 100 1 100 0
B 100 2 90 5
B 100 3 5 100
B 100 4 0 0
B 100 5 0 0
SELECT *,
CASE WHEN paid_amt > 0
AND paid_amt+nxp >= installment_amt
AND diff > 0
THEN 0
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND nxp > 0
THEN nxp
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND LAG(diff) OVER (PARTITION BY acc_name ORDER BY installment_no) > 0
THEN LAG(installment_amt-diff) OVER (PARTITION BY acc_name ORDER BY installment_no)
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
AND diff <0
THEN ABS(diff)+paid_amt
ELSE 0
END balance
FROM
(SELECT *,
CASE WHEN paid_amt > 0
AND paid_amt+nxp >= installment_amt
THEN paid_amt+nxp-installment_amt
WHEN paid_amt > 0
AND paid_amt+nxp < installment_amt
THEN (paid_amt+nxp)-installment_amt
END AS diff
FROM
(SELECT *,
CASE WHEN paid_amt < installment_amt THEN
LEAD(paid_amt) OVER (PARTITION BY acc_name ORDER BY installment_no)
ELSE 0 END nxp
FROM installment) V
) R
acc_name installment_amt installment_no paid_amt nxp diff balance
A 100 1 100 0 0 0
A 100 2 90 80 70 0
A 100 3 80 0 -20 30
A 100 4 0 0 null 0
A 100 5 0 null null 0
B 100 1 100 0 0 0
B 100 2 90 5 -5 5
B 100 3 5 0 -95 100
B 100 4 0 0 null 0
B 100 5 0 null null 0