0

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.

Improve this question

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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>
Improve this answer

1 Comment

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.