Select the most optimal combination

Analytics
Yan
Enthusiast

Select the most optimal combination

 

Hi all the great experts!

I am coming along my SQL Teradata learning journey, thanks to your help! Now I am struck with a new puzzle, and am hoping someone can help me.

 It is a bit long, please be patient to read through and I will try my best to explain...

 

I need to figure out, per order set, the fewest shipment having the least transit time value for the whole order lines.

For example:

option 1: 2 stores with average transit time of 3 days;

option 2: 3 stores with average transit time of 2.5 days;

Then option 1 wins. 

Below is a sample of 1 order, which contains 10 lines, each line is attached with transit time for store(s) that carries the product.

 

The only way I can think of is to do a 2-store combination, add up corresponding transit value if product is available (999 or blank = product not available in that store), search for the conbination that covers max # of lines with the lowest sum of transit time. 

 

Blow is the raw table (complete set for this order):

SO_No /SO_Item /ATP_Plant/ transit time
---------- ------- --------- ---
SO001 10 Store1 2
SO001 10 Store3 3
SO001 10 Store4 999
SO001 10 Store5 1
SO001 100 Store1 2
SO001 100 Store2 3
SO001 100 Store3 3
SO001 100 Store4 999
SO001 100 Store7 999
SO001 100 Store8 999
SO001 100 Store9 1
SO001 20 Store2 3
SO001 20 Store3 3
SO001 20 Store4 999
SO001 20 Store6 999
SO001 20 Store5 1
SO001 20 Store7 999
SO001 20 Store8 999
SO001 20 Store9 1
SO001 30 Store1 2
SO001 30 Store2 3
SO001 30 Store3 3
SO001 30 Store4 999
SO001 30 Store6 999
SO001 30 Store5 1
SO001 30 Store8 999
SO001 30 Store9 1
SO001 40 Store4 999
SO001 50 Store1 2
SO001 50 Store2 3
SO001 50 Store3 3
SO001 50 Store4 999
SO001 50 Store8 999
SO001 50 Store9 1
SO001 60 Store2 3
SO001 60 Store3 3
SO001 60 Store4 999
SO001 70 Store1 2
SO001 70 Store2 3
SO001 70 Store3 3
SO001 70 Store4 999
SO001 70 Store8 999
SO001 70 Store9 1
SO001 80 Store4 999
SO001 90 Store1 2
SO001 90 Store2 3
SO001 90 Store3 999
SO001 90 Store4 999
SO001 90 Store8 999

 

Here is my list of all two-store combination (partial result):

Branch1/ Branch2/ SO_Item/ transit time
Store1 Store5 10 1
Store1 Store7 10 2
Store1 Store9 10 2
Store1 Store8 10 2
Store1 Store2 10 2
Store1 Store3 10 2
Store1 Store4 10 2
Store1 Store6 10 2
Store2 Store5 10 1
Store2 Store1 10 2
Store2 Store3 10 3
Store3 Store5 10 1
Store3 Store1 10 2
Store3 Store7 10 3
Store3 Store2 10 3
Store3 Store8 10 3
Store3 Store9 10 3
Store3 Store4 10 3
Store3 Store6 10 3
Store4 Store5 10 1
Store4 Store1 10 2
Store4 Store3 10 3
Store6 Store5 10 1
Store6 Store5 20 1
Store6 Store5 30 1

 

Then I did the ranking with

select Branch1,Branch2, count(*),sum(transit time) from table1 group by Branch1,Branch2  order by 1,2;

 

Branch1,Branch2,Count(*),transit time (partial list)
Store1,Store2,8,18
Store1,Store3,8,18
Store1,Store4,6,12
Store1,Store5,6,12
Store1,Store6,7,11
Store1,Store7,6,12
Store1,Store8,6,12

 

So store 1 and 2 combination won. 

 

My questions are:

1. Is this the best approach?

2. How can I assign the store # back to each individual SO_Line, based on the most optimal result. In this case, store 1 will fill line 10, 30, 50, 70, 90, 100; store 3 will fill line 20, 60; line 40 and 80 are being backordered since no store can fill.

3. How can I repeat this for other orders in the pool? 

 

Thank you for your patience to read through!

 

Yan

 

 

1 REPLY
Junior Contributor

Re: Select the most optimal combination

You need to elaborate on the logic/rules :-)

 

fewest shipment having the least transit time value for the whole order lines

It's more important to get the lowest number of stores/shipments vs the fastest possible shipping?

E.g. store1: 1 item, 5 days, store2: 9 items, 3 days vs. all 10 items in a single store, but 2 days transit time?

 

Your example data shows always the same transit time for each store, is this actually correct (i.e. raw table is the result of a join)?

 

option 1: 2 stores with average transit time of 3 days;

option 2: 3 stores with average transit time of 2.5 days;

Average time calculated based on stores or items?
E.g. store1: 8 items, transit time 8 days, store2: 2 item2, 2 days. Should this result in avg(8*8 + 2*2) = 6.8 or avg(8 + 2) = 5.0?