How to subtract a data from multiple columns by considering previous reminder in PLSQL-CodePudding

I have this set of data where I want to sum BT(which is 0.8) data on Year,Month,EID basis and then subtract it in IN & make IN 0, then remaining(0.5) subtract from MT & make MT 0 then remaining(0.4) subtract from NLT & make NLT 0 then remaining if left (0.2) from VT & make VT 0

Please note, the subtraction happens in sequence IN>MT>NLT>VT

This is getting complex as I go on solving, if any one help me out with solution or clue would be much appreciated.

CodePudding user response：

It is unclear if you want to change the existing rows or aggregate and have a single row output. If it is the latter then you can use:

SELECT year,
       month,
       eid,
       GREATEST(SUM(bt-"IN"-mt-nlt-vt),0) AS bt,
       GREATEST(SUM("IN"-bt),0) AS "IN",
       GREATEST(LEAST(SUM("IN" mt-bt),SUM(mt)),0) AS mt,
       GREATEST(LEAST(SUM("IN" mt nlt-bt),SUM(nlt)),0) AS nlt,
       GREATEST(LEAST(SUM("IN" mt nlt vt-bt),SUM(vt)),0) AS vt
FROM   table_name
GROUP BY year, month, eid

Which, for the sample data (aggregating all your sample data into a single row and then expanding with various different months to test different conditions):

CREATE TABLE table_name (year, month, eid, bt, "IN", mt, nlt, vt) AS
SELECT 2022, 1, 123456, 0.8, 0.3, 0.1, 0.2, 0.2 FROM DUAL UNION ALL
SELECT 2022, 2, 123456, 0.8, 0.3, 0.1, 0.2, 0.1 FROM DUAL UNION ALL
SELECT 2022, 3, 123456, 0.8, 0.3, 0.1, 0.2, 0.3 FROM DUAL UNION ALL
SELECT 2022, 4, 123456, 0.8, 0.3, 0.1, 0.5, 0.3 FROM DUAL;

Outputs:

YEAR MONTH EID BT IN MT NLT VT

2022 1 123456 0 0 0 0 0

2022 2 123456 .1 0 0 0 0

2022 3 123456 0 0 0 0 .1

2022 4 123456 0 0 0 .1 .3

YEAR	MONTH	EID	BT	NLT	VT
2022	1	123456	0	0	0
2022	2	123456	.1	0	0
2022	3	123456	0	0	.1
2022	4	123456	0	.1	.3

db<>fiddle here