CODEWITHSUNDEEP
sqloracle-database
Komarov
Komarov

Reputation: 35

Return limit rows based on value in column

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:

enter image description here

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.

Upvotes: 0

Views: 43

Answers (2)

MT0
MT0

Reputation: 167902

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

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142705

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>

Upvotes: 1

Related Questions