I am trying to figure out how to write a functiom, which would run trough my dataset and fullfill several conditions.
Here is a quick look at the dataset
We have same Items with different quantities on different locations with different priorities.
The trick is to find a location with the lowest priority and add that quantity or just 1 to the location with the highest priority for the same item. Then move on the the second lowest priority and add that to the second largest and so on untill all pairs in the same item_no group are done, and if there is an uneven number of locations leave the last one allone, move on to the next item__no.
Priorities go from 1-26 , 1 being the highest and 26 the lowest.
for starters I just wanted to write something that would at least get me the result for the first group. I have item_no 100115 and to locations P26 and P30, P30 needs to receive that item from P26 because it has a higher priority
The desired output needs to be in the following format
in_location | from_location | item_no | quantity
I wrote this
SELECT in_loc, from_loc, item_no, quantity FROM ( SELECT (SELECT Store_no from sep WHERE Priority = 15) AS in_loc, Store_no AS from_loc, Item_no, Stock_Quantity AS quantity FROM sep WHERE Item_no = 100115) AS P WHERE in_loc <> from_loc
which got me the desired result for the first item
I am trying to figure out how to go about the complete function.
Creating and joining or self joining a copy of the table where i could say just a.store_no <> b.store_no
Do I need to state cases where one CASE is even number of locations in a Group and the other is uneven.
How do I go about the actual exchange , Do I do a COUNT of the number of locations for all items in a group and divide in pairs, do I pair GREATEST with the LEAST then move on to the second GREATEST or somehow use TOP nad BOTTOM with offset.
Maybe I need to define some local variables?
Sample table script
CREATE TABLE sep ( id INT PRIMARY KEY NOT NULL, item_no VARCHAR(12), store_no VARCHAR(4), quantity INT, priority INT); INSERT INTO sep (id, item_no, store_no, quantity, priority) VALUES (1, 100115, 'P026', 1, 15), (2, 100115, 'P030', 1, 19), (3, 100116, 'P015', 3, 14), (4, 100116, 'P017', 1, 2), (5, 100116, 'P002', 1, 11), (6, 100117, 'P021', 1, 9), (7, 100117, 'P033', 3, 20), (8, 100117, 'P008', 1, 22), (9, 100117, 'P007', 5, 5);
Also script for output if needed
CREATE TABLE output ( in_location VARCHAR(4), from_location VARCHAR(4), item_no VARCHAR(12), quantity INT);
Thanks for the response, just a little tweak needed with output now. This is the output from the answer
and I actually need this
Explanation about the output: we are looking at a large retailer, after doing inventory in some store locations it was found that certain items are unpaired when in fact they should be sold in pairs.
If you look at the first image in the post you can see that an item has an inventory multiple is x2 and quantity on location is 1 or 3 or whatever when in fact it should always be in pair. That is why I am trying to find storre_locations with lower priority which can give at least 1 item to a store_location with higher priority if they have the same unpaired item…
That’s why in my output I need to see that
Pxx(in_location with higher priority) Pxx(from_location with lower priority) Item_no(which item) quantity(receives how many/ 1 is enough)
Does this query gives the output which you need in?
WITH cte AS ( SELECT id, item_no, store_no, quantity, priority, LEAST(ROW_NUMBER() OVER (PARTITION BY item_no ORDER BY priority), 1 + COUNT(*) OVER (PARTITION BY item_no) - ROW_NUMBER() OVER (PARTITION BY item_no ORDER BY priority)) pair FROM sep ) SELECT id, item_no, store_no, quantity, priority, CASE WHEN priority = MIN(priority) OVER (PARTITION BY item_no, pair) THEN quantity + 1 ELSE quantity - 1 END new_quantity FROM cte ORDER BY item_no, priority