March 2, 2016 at 7:45 am
I managed to code the set-based iteration and it works pretty fast now!
March 2, 2016 at 8:39 am
Talvin Singh (3/2/2016)
is this an effective way of batching?
If you ask that from a logistic point of view, then I have no idea.
But if the logistical experts of your company has chosen that this is how it should be done, then I can give you some pointers on the code to write.
First, I will not try a set-based solution here. Maybe there is a possibility, but I doubt it - and I have too much headache right now to think about it. So you will have to iterate.
Before the iteration starts, the orders and their zone combinations should be in a table, that also has a column for the batch number. They are all NULL at the start, of course.
Step 1 in each iteration of the loop is to find a set of four (or less) zones that still has BatchNumber NULL. You'll need a query with TOP(1) and then an ORDER BY clause that prioritizes four-zones over three-zones etc. So the ORDER BY will probably have CASE WHEN fourPicks IS NULL THEN 1 ELSE 0 END as the first criterium, then similar for threeePicks, etc. You can tweak this order by is there has to be any other preference in this selection.
Step 2 is to assign the order just found to a new batch. That one is simple.
Step 3 is to find four other orders that are not yet assigned to a batch and that use the same zones, and assign them to the same batch. To find these orders, use code such as this in the query (assuming that you have the zones of the order found in step 1 in variables): WHERE onePick IN (@z1, @z2, @z3, @z4) AND (twoPicks IS NULL OR twoPicks IN (@z1, @z2, @z3, @z4)) AND (...)
Note that UPDATE TOP(4) works for this but will not allow you to specify ORDER BY, so SQL Server will then assign the first four orders it find by whatever search direction it chooses to use. If you want to have some form of priority in the assignments, use a SELECT TOP(4) ... ORDER BY, either in a subqeury or CTE for the UPDATE, or into a temprary table/table variable that is then later used to drive the update.
Since step 1 will always return a row if there are still unassigned orders, you can repeat the process until step 1 does not return a row anymore.
March 2, 2016 at 8:52 am
Ignore that I said is this an effective way of batching.
What I was meant to say is the way ive described, can it be done efficiently and effectively...or is there another way to code this...
I will go through your pointers and see if I am able to do it.
Thanks 🙂
March 4, 2016 at 2:58 am
.
March 4, 2016 at 6:21 am
Hi Talvin,
Sounds like you already have most of the algorithm figured out. After step 3 (in my previous post), check if you have hit the limit. If you have, fine. Otherwise, start the additional logic: find a fifth zone (how?? the algorithm for this is not clear for your post? Why did you choose 23, was it just a dice roll or is there some logic for that). Then find additional orders to add that stem from these five zones, using a modified version of the code for finding orders from four zones. Then again check if you are not at limit or if you have to add a sixth zone, and so on.
Once you have the algorithm figured out (and as I said, except for one piece of the puzzle you have), building it should not be a huge issue.
That being said, your queries will become complex as a result of the denormalized storage. The onePick, twoPicks, threePicks, and fourPicks columns are in fact a repeating group. If you normalize that design, your queries might (and I cannot stress that word enough!) become easier to build, and/or faster to execute.
March 4, 2016 at 6:56 am
I chose
March 4, 2016 at 7:10 am
Talvin Singh (3/4/2016)
I chose 23 as it was the first "extra" zone in the combination of 7, 14, 35, 43.
"First" by which criterium?
The word "first" only has a meaning if there is an implied or explicit ordering. When dealing with data in relational databases, that is never the case.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply