Skip to content
Advertisement

How to distribute stock quantity from a single cell to different aging brackets based on the the aging brackets quantities?

We have a table with item codes and current stock quantity and quantities in aging brackets whenever they were received (1-90, 91-120, etc) as follows:

Data

I need to distribute the STOCK_AS_ON_DATE quantity in the age brackets until no balance quantity is left.

Example:

Item A’s STOCK_AS_ON_DATE is 40377, in A1TO90 column we can see that 4000 quantities were received, in A91TO120, 4000 quantities were received, in A121TO270, 13000 quantities were received, we want to distribute the STOCK_AS_ON_DATE quantity in such a way in the other aging fields until the total of all the aging fields equals STOCK_AS_ON_DATE. In this item’s case, when we reach column A631TO720, 1377 quantity balance is left out of the 40377. If any aging bracket value is 0, we have to leave it as it is and move forward.

Example

Below is the Oracle query to create a sample table and data:

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
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


SET DEFINE OFF;
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);
COMMIT;

We have tried using the CASE statement but as we move further to other columns it’s becoming complicated.

CASE WHEN A1TO90 < STOCK_AS_ON_DATE THEN A1TO90 ELSE ( CASE WHEN A1TO90 >= STOCK_AS_ON_DATE THEN STOCK_AS_ON_DATE END ) END

There must be some other way which we are not aware of. Please suggest a simpler way to achieve our results. Thanks in advance.

Advertisement

Answer

A brute-force approach might be to use least() to decide whether to use the column quantity or the remainder from adding up all the columns to the left; with greatest() to stop any negative values. Something like:

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;

which is ugly and doesn’t scale very well, but isn’t really complicated per se. (This gives zero for the columns after you’ve exhausted the stock; your example had null for the final column for item A, but not sure if it matters; making it null is just a bit more complicated.)

If you want the total column you showed – again not sure you really do – you can use that as a CTE or inline view:

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,
    ...
  from AA_STK_AGING
) t;

db<>fiddle

If you are on 11g then you could unpivot your columns to rows:

select *
from AA_STK_AGING
unpivot (QUANTITY for BUCKET in (A1TO90 as 1, A91TO120 as 91, A121TO270 as 121, 
  A271TO360 as 271, A361TO450 as 361, A451TO540 as 451, A541TO630 as 541,
  A631TO720 as 631, A721PLUS as 721));

then perform a similar calculation using a running total:

select ITEM, STOCK_AS_ON_DATE, BUCKET,
  least(QUANTITY,
    greatest(0, STOCK_AS_ON_DATE + QUANTITY
      - sum(QUANTITY) over (partition by ITEM order by BUCKET))) as QUANTITY
from AA_STK_AGING
unpivot (QUANTITY for BUCKET in (A1TO90 as 1, A91TO120 as 91, A121TO270 as 121, 
    A271TO360 as 271, A361TO450 as 361, A451TO540 as 451, A541TO630 as 541,
    A631TO720 as 631, A721PLUS as 721)
);

and pivot that back to columns:

select *
from (
  select ITEM, STOCK_AS_ON_DATE, BUCKET,
    least(QUANTITY,
      greatest(0, STOCK_AS_ON_DATE + QUANTITY
        - sum(QUANTITY) over (partition by ITEM order by BUCKET))) as QUANTITY
  from AA_STK_AGING
  unpivot (QUANTITY for BUCKET in (A1TO90 as 1, A91TO120 as 91, A121TO270 as 121, 
    A271TO360 as 271, A361TO450 as 361, A451TO540 as 451, A541TO630 as 541,
    A631TO720 as 631, A721PLUS as 721)
  )
)
pivot (max(quantity) for (bucket) in (1 as A1TO90, 91 as A91TO120, 121 as A121TO270, 
  271 as A271TO360, 361 as A361TO450, 451 as A451TO540, 541 as A541TO630,
  631 as A631TO720, 721 as A721PLUS)
)
order by ITEM;

db<>fiddle

If you’re on 10g then you could manually unpivot and pivot but it probably isn’t worth the complication over the brute-force approach.

Both approaches give:

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

If you were on 12c or above you could look at match_recognize(), but you seem to be on either 10g or 11g.

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement