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

 

 

5 REPLIES 5
Ambassador

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?

Yan
Enthusiast

Re: Select the most optimal combination

Hi Dnoeth,

Happy new year!

Sorry it took me a while to figure out the detailed logics...

Here are what I gathered:

1. Max Transit time = 3 days, if over 3 days, not eligible

2. within 3 days limit, fewer shipments take priority.

For example: Option1: 2 stores with avg transit time of 2 days; Option2: 1 store with avg transit time of 3 days, then Option2 wins. 

Another example: Option1: 2 stores with avg TT of 2.5 days; option2: 3 stores with TT of 2 days, then option 1 wins

3. Avg TT is store/shipment based, not item based. 

4. If there is a tie, then need to compare distance as tie breaker.

5. All stores have static transit time that is the same for all orders. i.e. Store 1 TT is always 3 days, store 2 = 2 days, etc.

6. There are in total 10 stores for split check.

 

What I was thinking to do is to create a temp table and list out all the possible 3-store combinations for each line on the sales order and search for the best choice based on full order filled --> fewest shipment --> least avg TT days --> least avg distance.

But my chanllenge now are:

1. if a sales line has only 1 store carrying the inventory, it won't produce a 2-store or 3-store combination anymore. Then it is missed from the whole sales order. Is there a way I can enlist all 10 stores for each sales order line, even if the store does not have inventory? I tried Outer join, but it won't work for multiple lines...

2. possible combinations can be huge if the sales order has hundreds of lines...Performance can be slow...

3. How can I repeat the logic for other sales orders?

Hope you have the patience to read through my long story.

 

Thanks!

 

Yan

 

 

Yan
Enthusiast

Re: Select the most optimal combination

Sorry one correction to point #5:

"5. All stores have static transit time that is the same for all orders. i.e. Store 1 TT is always 3 days, store 2 = 2 days, etc."

==> incorrect. the store transit time is the same for all lines on the same order, but might be different for different sales order. Store 1 might have a 1-day transit for sales order 1, but 2-day TT for another order.

 

 

Highlighted
Teradata Employee

Re: Select the most optimal combination

Sorry if I go back to the initial subject, trying to figure out the problem before the solution.

 

You have orders with n items - here order SO001 with ten items, id from 10 to 100.

You have stores which hold some items and can ship them in a fixed transit time for the same order.

Here nine stores from Store1 to Store9.

  

You're trying to figure out the best combination to get all the items of the order, following those ordered priority rules :

  • within three days
  • from the least number of stores
  • in the shortest time possible

Did I get it all ?

 

If I get to the base datas, and I'm prolix on purpose, it would be something like :

Orders

create multiset volatile table mvt_orders, no log
( SO_No         char(6)
)
unique primary index (SO_No)
on commit preserve rows;

insert into mvt_orders values ('SO001');

create multiset volatile table mvt_orderitems, no log
( SO_No         char(6)
, SO_Item       smallint
)
primary index (SO_No)
on commit preserve rows;

insert into mvt_orderitems
    select top 10
           SO_No
         , 10 * row_number() over(order by null) as SO_Item
      from mvt_orders
cross join sys_calendar.calendar;

Stores

create multiset volatile table mvt_stores, no log
( ATP_Plant     char(6)
)
unique primary index (ATP_Plant)
on commit preserve rows;

insert into mvt_stores
select top 9
       'Store' || cast(row_number() over(order by null) as varchar(1))
  from sys_calendar.calendar;


create multiset volatile table mvt_storeitems, no log
( ATP_Plant     char(6)
, SO_Item       smallint
)
primary index (ATP_Plant)
on commit preserve rows;

insert into mvt_storeitems values ('Store1',  10);
insert into mvt_storeitems values ('Store3',  10);
insert into mvt_storeitems values ('Store4',  10);
insert into mvt_storeitems values ('Store5',  10);
insert into mvt_storeitems values ('Store1', 100);
insert into mvt_storeitems values ('Store2', 100);
insert into mvt_storeitems values ('Store3', 100);
insert into mvt_storeitems values ('Store4', 100);
insert into mvt_storeitems values ('Store7', 100);
insert into mvt_storeitems values ('Store8', 100);
insert into mvt_storeitems values ('Store9', 100);
insert into mvt_storeitems values ('Store2',  20);
insert into mvt_storeitems values ('Store3',  20);
insert into mvt_storeitems values ('Store4',  20);
insert into mvt_storeitems values ('Store6',  20);
insert into mvt_storeitems values ('Store5',  20);
insert into mvt_storeitems values ('Store7',  20);
insert into mvt_storeitems values ('Store8',  20);
insert into mvt_storeitems values ('Store9',  20);
insert into mvt_storeitems values ('Store1',  30);
insert into mvt_storeitems values ('Store2',  30);
insert into mvt_storeitems values ('Store3',  30);
insert into mvt_storeitems values ('Store4',  30);
insert into mvt_storeitems values ('Store6',  30);
insert into mvt_storeitems values ('Store5',  30);
insert into mvt_storeitems values ('Store8',  30);
insert into mvt_storeitems values ('Store9',  30);
insert into mvt_storeitems values ('Store4',  40);
insert into mvt_storeitems values ('Store1',  50);
insert into mvt_storeitems values ('Store2',  50);
insert into mvt_storeitems values ('Store3',  50);
insert into mvt_storeitems values ('Store4',  50);
insert into mvt_storeitems values ('Store8',  50);
insert into mvt_storeitems values ('Store9',  50);
insert into mvt_storeitems values ('Store2',  60);
insert into mvt_storeitems values ('Store3',  60);
insert into mvt_storeitems values ('Store4',  60);
insert into mvt_storeitems values ('Store1',  70);
insert into mvt_storeitems values ('Store2',  70);
insert into mvt_storeitems values ('Store3',  70);
insert into mvt_storeitems values ('Store4',  70);
insert into mvt_storeitems values ('Store8',  70);
insert into mvt_storeitems values ('Store9',  70);
insert into mvt_storeitems values ('Store4',  80);
insert into mvt_storeitems values ('Store1',  90);
insert into mvt_storeitems values ('Store2',  90);
insert into mvt_storeitems values ('Store3',  90);
insert into mvt_storeitems values ('Store4',  90);
insert into mvt_storeitems values ('Store8',  90);


create multiset volatile table mvt_storeorders, no log
( SO_No         char(6)
, ATP_Plant     char(6)
, TransitTime   integer
)
primary index (SO_No)
on commit preserve rows;

insert into mvt_storeorders values ('SO001', 'Store1',   2);
insert into mvt_storeorders values ('SO001', 'Store2',   3);
insert into mvt_storeorders values ('SO001', 'Store3',   3);
insert into mvt_storeorders values ('SO001', 'Store4', 999);
insert into mvt_storeorders values ('SO001', 'Store5',   1);
insert into mvt_storeorders values ('SO001', 'Store6', 999);
insert into mvt_storeorders values ('SO001', 'Store7', 999);
insert into mvt_storeorders values ('SO001', 'Store8', 999);
insert into mvt_storeorders values ('SO001', 'Store9',   1);

And the query to match your dataset :

select odr.SO_No, odr.SO_Item, sti.ATP_Plant, sto.TransitTime
  from mvt_orderitems  as odr
  join mvt_storeitems  as sti  on sti.SO_Item   = odr.SO_Item
  join mvt_storeorders as sto  on sto.SO_No     = odr.SO_No
                              and sto.ATP_Plant = sti.ATP_Plant;

 

Ambassador

Re: Select the most optimal combination

I think I got a solution (using temporary tables & a loop in a Stored Procedure), but need some more answers:

Is there a maximum number of lines per order?

And how many orders do you have to process?

Do you want to process those orders at once or sequentially?