I've got this piece of code:

SELECT t1.sku_id, t1.putaway_group, t1.shortage, t4.location_id, t4.qty_on_hand
    FROM 
    (WHERE clauses)t1
   
LEFT JOIN
        (
        SELECT *
        FROM   (
          SELECT location_id, sku_id, qty_on_hand,
                DENSE_RANK() OVER ( PARTITION BY sku_id ORDER BY qty_on_hand DESC ) AS rnk
          FROM   inventory
          WHERE substr(zone_1,1,5) IN ('TOTEB','TOTEC')
        )
        WHERE  rnk = 1
        ORDER BY 2 DESC
        )t4
        ON t3.sku_id = t4.sku_id

Where the output is:

What i want to achieve is to return as many rows from location_id as shortage require. For example if shortage is -84 THEN as an output for SKU: 02295441 i want to return 6 rows because (6*16 = 96) which will cover my shortage. Not really sure if it's possible or if yes then how to write a where/having clause to limit output rows. Currently I'm just doing it through power query in excel, but just wondering if it's possible straight from sql. Thanks in advance.

CodePudding user response：

You can use the SUM analytic function:

SELECT t1.sku_id,
       t1.putaway_group,
       t1.shortage,
       t4.location_id,
       t4.qty_on_hand
FROM   /*(WHERE clauses)*/ t1
       LEFT JOIN (
         SELECT location_id,
                sku_id,
                qty_on_hand,
                SUM(qty_on_hand) OVER (
                  PARTITION BY sku_id
                  ORDER BY qty_on_hand DESC, ROWNUM
                ) AS total_qty
         FROM   inventory
         WHERE  zone_1 LIKE 'TOTEB%'
         OR     zone_1 LIKE 'TOTEC%'
       ) t4
       ON (   t1.sku_id = t4.sku_id
          AND -t1.shortage > t4.total_qty - t4.qty_on_hand )

Which, for the sample data:

CREATE TABLE t1 (sku_id, putaway_group, shortage) AS
SELECT 'SKU1', 'TEXTILES', -84 FROM DUAL UNION ALL
SELECT 'SKU2', 'PLASTICS', -13 FROM DUAL;

CREATE TABLE inventory(location_id, sku_id, qty_on_hand, zone_1) AS
SELECT LEVEL, 'SKU1', LEAST(LEVEL * 4, 16), 'TOTEB' || CHR(64   LEVEL) FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL
SELECT LEVEL, 'SKU2', LEVEL, 'TOTEC' || CHR(64   LEVEL) FROM DUAL CONNECT BY LEVEL <= 6;

Outputs:

fiddle

CodePudding user response：

Due to lack of sample data, my CTE represents (simplified) result you currently have; if you apply row_number function to it and then return rows that satisfy the condition you mentioned (see line #22), you might get what you want:

SQL> with data (sku_id, shortage, location_id, qty_on_hand) as
  2    (select 1, -84, 3, 16 from dual union all
  3     select 1, -84, 2, 16 from dual union all
  4     select 1, -84, 5, 16 from dual union all
  5     select 1, -84, 5, 16 from dual union all
  6     select 1, -84, 5, 16 from dual union all
  7     select 1, -84, 6, 16 from dual union all
  8     select 1, -84, 1, 16 from dual union all
  9     select 1, -84, 2, 16 from dual union all
 10     select 1, -84, 1, 16 from dual union all
 11     select 1, -84, 2, 16 from dual union all
 12     --
 13     select 2, -20, 1, 10 from dual
 14    ),
 15  temp as
 16    (select d.*,
 17       row_number() over (partition by sku_id order by qty_on_hand) rnk
 18       from data d
 19    )
 20  select *
 21  from temp
 22  where rnk <= ceil(abs(shortage) / qty_on_hand);

    SKU_ID   SHORTAGE LOCATION_ID QTY_ON_HAND        RNK
---------- ---------- ----------- ----------- ----------
         1        -84           3          16          1  --> SKU_ID = 1 begins here
         1        -84           2          16          2
         1        -84           5          16          3
         1        -84           5          16          4
         1        -84           5          16          5
         1        -84           6          16          6  --> SKU_ID = 1 ends here; 6 rows
         2        -20           1          10          1

7 rows selected.

SQL>