December 23, 2017 at 8:44 am
I am struggling to figure out how take combinations of surplus item units and offset them against items needed (#items below) for an order. My understanding of the business case is that larger sizes of the items should be offset first using exact combinations of smaller surplus items. The item sizes are known in advance (for example one item might come in 40,20,10,5,2 sizes. I might end up with surplus 5's to offset against a need for some 20's for example. How can I do this effectively in T-SQL? Thanks in advance for you assistance.
--Size and quantities needed to place an order. To be offset by surplus.
CREATE TABLE #Items (Size DECIMAL(10,1), Quantity INT)
INSERT INTO #Items (Size, Quantity) VALUES (5,3)
INSERT INTO #Items (Size, Quantity) VALUES (20,2)
SELECT * FROM #Items
DROP TABLE #Items
--Size and quantities available to offset the order above.
CREATE TABLE #Surplus (Size DECIMAL(10,1), Quantity INT)
INSERT INTO #Surplus (Size, Quantity) VALUES (1,4)
INSERT INTO #Surplus (Size, Quantity) VALUES (2,6)
INSERT INTO #Surplus (Size, Quantity) VALUES (10,1)
SELECT * FROM #Surplus
DROP TABLE #Surplus
--AFTER offsetting
CREATE TABLE #ItemResults (Size DECIMAL(10,1), Quantity INT)
--Taking the last unit of size two and three units of size one to offset one unit of size five.
INSERT INTO #ItemResults (Size, Quantity) VALUES (5,2)
--Taking 1 unit of size 10 and 5 units of size 2 to remove the need to order one 20 unit.
INSERT INTO #ItemResults (Size, Quantity) VALUES (20,1)
SELECT * FROM #ItemResults
DROP TABLE #ItemResults
CREATE TABLE #SurplusResults (Size DECIMAL(10,1), Quantity INT)
INSERT INTO #SurplusResults (Size, Quantity) VALUES (1,1)
INSERT INTO #SurplusResults (Size, Quantity) VALUES (2,0)
INSERT INTO #SurplusResults (Size, Quantity) VALUES (20,1)
SELECT * FROM #SurplusResults
DROP TABLE #SurplusResults
December 26, 2017 at 8:52 am
What are your expected results?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 26, 2017 at 9:17 am
Drew,
My expected results are contained in the two temporary tables #ItemResults and #SurplusResults. They represent the results after having offset order quantities with available surplus quantities. If there is a logical conflict, I choose to offset larger sizes first if there is an available combination for that scenario
Thanks,
-Michael
January 4, 2018 at 3:08 am
Let me try and restate the problem and can msmith confirm whether my understanding is correct.
Items come in different carton quantities (40,20,10,5,2) and at any time we will only have a discreet number of each carton size available
An order can be for any size that can be fulfilled from one or more carton quantities so in the above example you can't order 1 or 3 because the smallest carton size is 2, but you can order 7 because that is 5+2 or 8 because that is 2+2+2+2
The objective is to complete an order with the fewest number of boxes possible so if an order comes in for 40 units we would prove a 40 carton if one is available. If not then we would try 20+20, then 20+10+10 then 10+10+10+10+10 etc.. there would need to be a business rule to determine whether 20+10+5+5 was preferred to 10+10+10+10
If we have two orders for 40 but only one 40 in stock then the allocations for the orders need to be different.
If we have 1x40, 3x20, 10x10 in stock
and orders for 40,40,30,20
The fulfilment could look like
40: 1x40
40: 2x20
30: 1x20, 1x10
20: 2x10 (no 20s left)
OR it could be
40:1x40
40: 1x20, 2x10 (save the 20 for the 20 order)
30: 1x20, 1x10
20: 1x20
OR it could be
40: 1x40
40: 2x20
30: 3x10
20: 1x20
I can't think of a clever way to solve this in a set based method and I think this would be a justified use of cursors. If it were me I would run a query to identify all of the orders that can be fulfilled from a single carton and create a temp table to assign the cartons to the orders, write the orders to a temp table as fulfilled and write the cartons to a temp table as allocated. I would then cursor through the remaining orders (sorted based on some business rule) and iteratively identify the fewest number of cartons required to fulfil the order and update the three temp tables.
This will probably not produce the most optimal solution but it will get you close (perhaps within 80% of optimal) but there will be diminishing returns from the more complex rules and I would argue that the time would be better spent forecasting demand so that you have the right sized cartons in stock.
January 4, 2018 at 3:00 pm
aaron.reese,
tldr; Thanks. I think I implemented something very close to what you are suggesting though my implementation has certain flaws.
Your understanding of the problem is correct and quite well explicated. I believe my line of thinking and approach on how to solve the problem mirrors yours very closely. I was initially 'thrown for a loop' not knowing how to construct a clever set based solution and decided that I was going to have to iterate via while loop (or cursor). I learned most of my programming in set-based fashion (MS SQL server), so this started to stretch the mind nicely. I made some insight to the problem since initially posted which I will relay here (certainly haven't made it to an optimal solution yet, hence why I very much leave it open...like you indicated it will be 'maybe' 80% optimal):
I decided to seek an algorithmic solution and had the insight that this problem is very similar to the problem of having a dollar bill and finding all possible combinations of half dollars, quarters, dimes, nickels and pennies that add up to a buck. Since I knew the possible stock sizes (40,20,10,1 for example) for each item, I could use a brute force/recursive approach to initially construct every possible solution (definitely would prefer a query here, but I took what I had at the time to start with). I did that 'by hand' using the Java code that I leveraged online to construct a base table of solutions (possible combinations that lead up to each item size using smaller sizes and a version of your three proposed temp tables I think).
Now that I had all the possible solutions (assuming I didn't miss some by my hackish approach), I created a new column and prioritized each solution as youu succinctly explained using SQL ranking functions, handling the case of a 'tie' (20 + 10 + 10 = 40 = 10 + 10 + 10 + 10) likely by choosing randomly at first. From there I constructed two more temporary tables holding the orders and excess stock at each respective iteration. I then iterated down in item size through the orders, checking each time to attempt get the highest priority combo if it happened to be available as excess. I added another column to tag each item after it had been iterated and let the while loop proceed until there was nothing left to check. The excess temp table gets updates as does the order temp table at each iteration.
This solution has some drawbacks but may end up saving the day for the integration need that I have currently (pending coming up with a more optimal approach). There are some problems worth admitting directly:
1) As new item sizes are added, I'll have to brute force and prioritize the solutions as I had done before (maintenance problem but doable)
2) I picked randomly in the case of a tie. A smarter solution would actually fork and look at both options and see if either path of the 'tree' resulted in better item utilization at smaller sizes (for example we might choose not to use the 10 + 10 + 10 + 10 = 40 solution since the 10's might be used in another combo more effectively.
3) Had I implemented part 2), then the remaining election could likely be made at random (fine) or based on item price (better) for each combo at the time of order or some other business logic that is too minute to worry with.
4) If I end up going with my current solution, I am absolutely going to need to create excellent test cases to ensure it doesn't break down or suggest outlandish things going forward. Another opportunity for growth on my end of the programming learning world.
Thanks again for looking at this and I hope my rambling makes sense for those of you who read this far.
-Michael
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply