In general when looking at problems with these types of algorithms, it's helpful if you have a data sample complex enough to show the issue that we can poke at.
However, the algorithm itself is annoyingly complex when you have multiple master cartons. The reason for this is the number of different assemblies you can make to 'fill gaps' depending on the order that's chosen. For example, if you have 5 master cartons with overlap, how do you determine which master carton to grab that fits well with another master carton that doesn't? It's not simply selecting the master carton, but multiple possible master cartons depending on who overlaps what.
Generic example: I have master carton that holds A,B,C, and another that holds A,B,C,D, and yet a third master carton that holds D,E, and F. An order comes in for A through F. If the 4 piece master carton gets selected (the largest overlap) and pick the other two items, then I miss the opportunity to use the DEF Master Carton, which could be used with the ABC carton. Start stacking these possibilities and it starts looking like a puzzle assembly.
How do you get these to mesh well? Well, really... you don't. What you have to do is slap EVERY combination of non-overlapping packages over the order, and see which has the least picks. This gets even MORE complex if you start looking at quantities, because overlaps no longer can be excluded. Let's say I get an order for 4 As, 2 Bs, 5Cs, and 1 D. Now I grab an ABCD Master carton, an ABC Master Carton, and pick 2 As and 3 Cs. So as this algorithm attempts to figure it out, it has to reduce quantities as you apply packages.
At this point, I'd have to see the full business expectation and packaging methods, standard order ideas, and the like to determine the algorithm. It would have to be very custom because there's no 'perfect way', just a lot of 'good enough' ideas (at least to me) that I would have to pick the best of to apply to the expectations.
However, the tool to apply wouldn't be my first question here. I'd want to nail down the algorithm I want to work with, then pick the tool. I really don't see a difference here between T-SQL, .NET, and SSIS for the implimentation until you get into the nitty gritty of the algorithm, then you'd just pick the easiest tool to implement it with.
On a side note, I've got an inkling of a thought about pre-assembling the carton possibilities (dynamically in theory) and then applying all the carton pre-builds against the order to see what had the least 'picks', but I don't really have time to assemble a full bore test process against that to see if it has merit.
- Craig Farrell
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA