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:
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.
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 NOCACHENOPARALLELMONITORING;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 ) ENDThere 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 A721PLUSfrom 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 TOTALfrom ( select ITEM, STOCK_AS_ON_DATE, least(A1TO90, STOCK_AS_ON_DATE) as A1TO90, from AA_STK_AGING) t;If you are on 11g then you could unpivot your columns to rows:
select *from AA_STK_AGINGunpivot (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 QUANTITYfrom AA_STK_AGINGunpivot (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;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 40377B 48 48 0 0 0 0 0 0 0 0 48C 3269 3269 0 0 0 0 0 0 0 0 3269D 3330 3000 330 0 0 0 0 0 0 0 3330E 13225 3000 0 10225 0 0 0 0 0 0 13225F 1759 526 478 0 0 0 502 253 0 0 1759G 2528 0 2528 0 0 0 0 0 0 0 2528H 163 163 0 0 0 0 0 0 0 0 163I 158 158 0 0 0 0 0 0 0 0 158J 254 254 0 0 0 0 0 0 0 0 254K 75864 20400 0 34800 20664 0 0 0 0 0 75864L 18074 15525 0 0 2549 0 0 0 0 0 18074M 45247 20000 0 0 25247 0 0 0 0 0 45247N 3332 3332 0 0 0 0 0 0 0 0 3332O 287 287 0 0 0 0 0 0 0 0 287P 338 338 0 0 0 0 0 0 0 0 338Q 892 650 242 0 0 0 0 0 0 0 892If you were on 12c or above you could look at match_recognize(), but you seem to be on either 10g or 11g.