Return limit rows based on value in column

Question

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.

MT0 · Accepted Answer · 2022-11-23 21:26:03Z

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:

SKU_ID	PUTAWAY_GROUP	SHORTAGE	LOCATION_ID	QTY_ON_HAND
SKU1	TEXTILES	-84	4	16
SKU1	TEXTILES	-84	5	16
SKU1	TEXTILES	-84	6	16
SKU1	TEXTILES	-84	7	16
SKU1	TEXTILES	-84	8	16
SKU1	TEXTILES	-84	9	16
SKU2	PLASTICS	-13	6	6
SKU2	PLASTICS	-13	5	5
SKU2	PLASTICS	-13	4	4

fiddle

Littlefoot · Accepted Answer · 2022-11-23 20:48:32Z

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>

This will only work if every qty_on_hand is identical for the SKU.

Collectives™ on Stack Overflow

Return limit rows based on value in column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related