Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE AA_STK_AGING
(
  ITEM              NVARCHAR2(10),
  STOCK_AS_ON_DATE  NUMBER,
  A1TO90            NUMBER,
  A91TO120          NUMBER,
  A121TO270         NUMBER,
  A271TO360         NUMBER,
  A361TO450         NUMBER,
  A451TO540         NUMBER,
  A541TO630         NUMBER,
  A631TO720         NUMBER,
  A721PLUS          NUMBER
);
begin

Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('A', 40377, 4000, 4000, 13000, 
    0, 8340, 1660, 8000, 4400, 
    223380);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('B', 48, 50, 0, 160, 
    45, 50, 90, 70, 120, 
    2913);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('C', 3269, 7100, 6600, 24100, 
    9925, 0, 27975, 23700, 20000, 
    929437);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('D', 3330, 3000, 4400, 6100, 
    1700, 0, 10400, 3000, 18200, 
    628437);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('E', 13225, 3000, 0, 25100, 
    4500, 4500, 2900, 110, 14800, 
    171590);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('F', 1759, 526, 478, 0, 
    0, 0, 502, 484, 0, 
    13308);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('G', 2528, 0, 3500, 0, 
    3500, 0, 3000, 0, 0, 
    43706);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('H', 163, 950, 550, 2850, 
    1480, 735, 2025, 1450, 875, 
    52278);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('I', 158, 520, 200, 914, 
    350, 420, 540, 300, 150, 
    10557);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('J', 254, 750, 350, 1650, 
    1050, 570, 1300, 1070, 710, 
    25886);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('K', 75864, 20400, 0, 34800, 
    32400, 0, 32400, 28800, 34800, 
    701550);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('L', 18074, 15525, 0, 0, 
    41480, 27630, 0, 28850, 0, 
    861545);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('M', 45247, 20000, 0, 0, 
    35000, 60000, 0, 0, 0, 
    834240);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('N', 3332, 4125, 0, 5625, 
    2690, 3015, 2035, 3380, 5290, 
    152709);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('O', 287, 522, 132, 1878, 
    864, 66, 725, 795, 852, 
    36503);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('P', 338, 401, 134, 1113, 
    484, 127, 627, 745, 813, 
    18715);
Insert into AA_STK_AGING
   (ITEM, STOCK_AS_ON_DATE, A1TO90, A91TO120, A121TO270, 
    A271TO360, A361TO450, A451TO540, A541TO630, A631TO720, 
    A721PLUS)
 Values
   ('Q', 892, 650, 1000, 3200, 
    1800, 0, 3400, 3600, 2200, 
    113347);

end;
/
1 rows affected

select ITEM,
  STOCK_AS_ON_DATE,
  least(A1TO90,    STOCK_AS_ON_DATE) as A1TO90,
  least(A91TO120,  greatest(0, STOCK_AS_ON_DATE - A1TO90)) as A91TO120,
  least(A121TO270, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120)) as A121TO270,
  least(A271TO360, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270)) as A271TO360,
  least(A361TO450, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360)) as A361TO450,
  least(A451TO540, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450)) as A451TO540,
  least(A541TO630, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540)) as A541TO630,
  least(A631TO720, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540 - A541TO630)) as A631TO720,
  least(A721PLUS,  greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540 - A541TO630 - A631TO720)) as A721PLUS
from AA_STK_AGING;


ITEM
STOCK_AS_ON_DATE
A1TO90
A91TO120
A121TO270
A271TO360
A361TO450
A451TO540
A541TO630
A631TO720
A721PLUS


A
40377
4000
4000
13000
0
8340
1660
8000
1377
0

B
48
48
0
0
0
0
0
0
0
0

C
3269
3269
0
0
0
0
0
0
0
0

D
3330
3000
330
0
0
0
0
0
0
0

E
13225
3000
0
10225
0
0
0
0
0
0

F
1759
526
478
0
0
0
502
253
0
0

G
2528
0
2528
0
0
0
0
0
0
0

H
163
163
0
0
0
0
0
0
0
0

I
158
158
0
0
0
0
0
0
0
0

J
254
254
0
0
0
0
0
0
0
0

K
75864
20400
0
34800
20664
0
0
0
0
0

L
18074
15525
0
0
2549
0
0
0
0
0

M
45247
20000
0
0
25247
0
0
0
0
0

N
3332
3332
0
0
0
0
0
0
0
0

O
287
287
0
0
0
0
0
0
0
0

P
338
338
0
0
0
0
0
0
0
0

Q
892
650
242
0
0
0
0
0
0
0


select t.*,
  A1TO90 + A91TO120 + A121TO270 + A271TO360 + A361TO450 + A451TO540 + A541TO630 + A631TO720 + A721PLUS as TOTAL
from (
  select ITEM,
    STOCK_AS_ON_DATE,
    least(A1TO90,    STOCK_AS_ON_DATE) as A1TO90,
    least(A91TO120,  greatest(0, STOCK_AS_ON_DATE - A1TO90)) as A91TO120,
    least(A121TO270, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120)) as A121TO270,
    least(A271TO360, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270)) as A271TO360,
    least(A361TO450, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360)) as A361TO450,
    least(A451TO540, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450)) as A451TO540,
    least(A541TO630, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540)) as A541TO630,
    least(A631TO720, greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540 - A541TO630)) as A631TO720,
    least(A721PLUS,  greatest(0, STOCK_AS_ON_DATE - A1TO90 - A91TO120 - A121TO270 - A271TO360 - A361TO450 - A451TO540 - A541TO630 - A631TO720)) as A721PLUS
  from AA_STK_AGING
) t;


ITEM
STOCK_AS_ON_DATE
A1TO90
A91TO120
A121TO270
A271TO360
A361TO450
A451TO540
A541TO630
A631TO720
A721PLUS
TOTAL


A
40377
4000
4000
13000
0
8340
1660
8000
1377
0
40377

B
48
48
0
0
0
0
0
0
0
0
48

C
3269
3269
0
0
0
0
0
0
0
0
3269

D
3330
3000
330
0
0
0
0
0
0
0
3330

E
13225
3000
0
10225
0
0
0
0
0
0
13225

F
1759
526
478
0
0
0
502
253
0
0
1759

G
2528
0
2528
0
0
0
0
0
0
0
2528

H
163
163
0
0
0
0
0
0
0
0
163

I
158
158
0
0
0
0
0
0
0
0
158

J
254
254
0
0
0
0
0
0
0
0
254

K
75864
20400
0
34800
20664
0
0
0
0
0
75864

L
18074
15525
0
0
2549
0
0
0
0
0
18074

M
45247
20000
0
0
25247
0
0
0
0
0
45247

N
3332
3332
0
0
0
0
0
0
0
0
3332

O
287
287
0
0
0
0
0
0
0
0
287

P
338
338
0
0
0
0
0
0
0
0
338

Q
892
650
242
0
0
0
0
0
0
0
892
ITEM	STOCK_AS_ON_DATE	A1TO90	A91TO120	A121TO270	A271TO360	A361TO450	A451TO540	A541TO630	A631TO720
A	40377	4000	4000	13000	0	8340	1660	8000	1377
B	48	48	0	0	0	0	0	0	0
C	3269	3269	0	0	0	0	0	0	0
D	3330	3000	330	0	0	0	0	0	0
E	13225	3000	0	10225	0	0	0	0	0
F	1759	526	478	0	0	0	502	253	0
G	2528	0	2528	0	0	0	0	0	0
H	163	163	0	0	0	0	0	0	0
I	158	158	0	0	0	0	0	0	0
J	254	254	0	0	0	0	0	0	0
K	75864	20400	0	34800	20664	0	0	0	0
L	18074	15525	0	0	2549	0	0	0	0
M	45247	20000	0	0	25247	0	0	0	0
N	3332	3332	0	0	0	0	0	0	0
O	287	287	0	0	0	0	0	0	0
P	338	338	0	0	0	0	0	0	0
Q	892	650	242	0	0	0	0	0	0