Cartesian Product? 1

[mymou]

Hi All

As an simple example, I have a number of suppliers who all supply products which may be a full or a subset of all products.

In order to find the cheapest possible method of supply - I am using a cartesian product (which works very well) but would like to explore other alternatives (for obvious reasons). Any ideas?

Example:
Query [12]:
SELECT ProductsSupplied.ProductID, ProductsSupplied.SupplierID, [price]+[mail cost] AS Pricez
FROM Suppliers INNER JOIN ProductsSupplied ON Suppliers.SupplierID = ProductsSupplied.SupplierID
WHERE (((ProductsSupplied.ProductID)=12));

ie - finds which suppliers supply a particular product and at what cost.

SELECT [12].pricez + [13].pricez + [14].pricez AS total, [13].SupplierID, [14].SupplierID, [12].SupplierID
FROM 13, 14, 12

Which lists all possible combination of prices - and hence easy to find the cheapest.

Any better methods?

Stew

 

[tomschafer]

Try..

select
min(12.Pricez)
from
12
group by
12.ProductID

 

[mymou]

Nice try Pezamystik

Not so easy im afraid. I didn't explain very well that there are other products (12, 13 and 14 in this case). Also there is a mistake in the above code as the mail cost shouldn't be added to each product - but rather as part of the cartesian product (one mail charge for each supplier)

The issue is that any supplier or combination of supplier may offer the best overall deal.

In data terms

ProductID 12
SupplierID Pricez
1 €30.00
2 €20.00
5 €30.00

ProductID 13
SupplierID Pricez
1 €35.00
2 €20.00
5 €30.00

ProductID 14
SupplierID Pricez
1 €30.00
3 €20.00
5 €30.00

All combinations
Total 12_SupplierID 13_SupplierID 14_SupplierID
€60.00 2 2 3
€70.00 1 2 3
€70.00 2 2 5
€70.00 2 5 3
€70.00 5 2 3
€70.00 2 2 1
€75.00 2 1 3
€80.00 5 2 1
€80.00 1 2 1
€80.00 5 5 3
€80.00 2 5 5
€80.00 5 2 5
€80.00 2 5 1
€80.00 1 2 5
€80.00 1 5 3
€85.00 1 1 3
€85.00 2 1 1
€85.00 2 1 5
€85.00 5 1 3
€90.00 1 5 5
€90.00 5 5 1
€90.00 1 5 1
€90.00 5 5 5
€95.00 5 1 1
€95.00 1 1 5
€95.00 5 1 5
€95.00 1 1 1

From this the total mailing cost can be attibuted - and hence the final costs.

Mail Costs
Supplier ID
1 €5.00
2 €0.00
3 €10.00
4 €5.00
5 €0.00

total 12.SupplierID 13.SupplierID 14.SupplierID Mail
€70.00 2 2 3 10
€70.00 2 2 5 0
€75.00 2 2 1 5
€80.00 5 2 3 10
€80.00 2 5 5 0
€80.00 5 2 5 0
€80.00 2 5 3 10
€85.00 1 2 5 5
€85.00 5 2 1 5
€85.00 1 2 1 5
€85.00 1 2 3 15
€85.00 2 5 1 5
€90.00 2 1 1 5
€90.00 5 5 5 0
€90.00 5 5 3 10
€90.00 2 1 3 15
€90.00 2 1 5 5
€95.00 1 5 3 15
€95.00 1 5 1 5
€95.00 5 5 1 5
€95.00 1 5 5 5
€100.00 1 1 3 15
€100.00 5 1 3 15
€100.00 5 1 1 5
€100.00 1 1 5 5
€100.00 5 1 5 5
€100.00 1 1 1 5

Does that make it any clearer?

Is there a better way?

Stew

 

[tomschafer]

I'm probably missing something but try this...

SELECT
Suppliers.SupplierID,
ProductsSupplied.ProductID
FROM
ProductsSupplied,
Suppliers,
(SELECT
ProductsSupplied.ProductID AS ProductID,
min([price]) AS Pricez
FROM
Suppliers,
ProductsSupplied
WHERE
Suppliers.SupplierID = ProductsSupplied.SupplierID
GROUP BY
ProductsSupplied.ProductID) SubQuery
WHERE
ProductsSupplied.SupplierID=Suppliers.SupplierID and
Suppliers.ProductID=SubQuery.ProductID and
ProductsSupplied.Price=SubQuery.Price

 

[mymou]

Pezamystik

No mail costs - you may have the cheapest prices - but not the cheapest total cost.

Stew

PS Didn't work - and I run a mile at the sight of a subquery!

 

[r937]

stew, are you looking for cheapest combination all from the same supplier?

you pointed out that a given supplier may not supply all products

are you interested in only those suppliers who supply all products, 12, 13, and 14?

that sounds like an inner join to me

select S.SupplierID
     , min( P12.[price]
          + P13.[price]
          + P14.[price]
          + S.[mail cost] )
  from Suppliers S
inner
  join ProductsSupplied P12
    on S.SupplierID = P12.SupplierID
   and                P12.ProductID=12
inner
  join ProductsSupplied P13
    on S.SupplierID = P13.SupplierID
   and                P13.ProductID=13
inner
  join ProductsSupplied P14
    on S.SupplierID = P14.SupplierID
   and                P14.ProductID=14
group
    by S.SupplierID

if you can take 12 and 13 from supplier A, along with A's mail cost, and take 14 from supplier B, along with B's mail cost, and still come out with a cheaper overall cost than any single supplier of all three with only one mail cost, then that's even messier

it's messy because you have to apply the mail cost carefully, if two or three of the products in any combination are from the same supplier

{deity} help you if you want to do it for four products

rudy

http://rudy.ca/

 

[mymou]

Hi r937

You have hit the nail on the head. I don't care where the products come from as long as it is the cheapest alternative - hence the use of a cartesian product. Messy I know and some restrictions (especially if the different number of products ordered are increased or the number of possible suppliers)

So in my example (which is returned from a CP)
€70.00
Supplier 2 - book12
Supplier 2 - book13
Supplier 3 (or 5) - book14
Costs €70.00

and
Supplier 5 - All Books
Costs €90.00

Big difference. Any Ideas?

Stew

 

[r937]

i tested this in access 97, so i think it works

first define the following query to pull out all the suppliers of products 12, 13, or 14

select S.SupplierID
     , [mail cost] AS mailcost
     , ProductID
     , price
  from ProductsSupplied AS P
     , Supplier AS S
 where P.SupplierID = S.SupplierID
   and P.ProductID in (12,13,14)

save that query in access as PSquery

now do the cross-join

select S12.SupplierID
     , S13.SupplierID
     , S14.SupplierID
     , S12.Price+S13.Price+S14.Price
        as totalprice
     , iif(S12.mailcost=S13.mailcost
          ,iif(S13.mailcost=S14.mailcost
              ,S12.mailcost
              ,S12.mailcost+S14.mailcost)
          ,iif(S13.mailcost=S14.mailcost
              ,S12.mailcost+S13.mailcost
              ,S12.mailcost+S13.mailcost
                           +S14.mailcost)
       ) as totalmailcost
     , S12.Price+S13.Price+S14.Price
      +iif(S12.mailcost=S13.mailcost
          ,iif(S13.mailcost=S14.mailcost
              ,S12.mailcost
              ,S12.mailcost+S14.mailcost)
          ,iif(S13.mailcost=S14.mailcost
              ,S12.mailcost+S13.mailcost
              ,S12.mailcost+S13.mailcost
                           +S14.mailcost)
      ) as totaltotal
  from PSquery S12 
     , PSquery S13
     , PSquery S14
 where S12.ProductID = 12 
   and S13.ProductID = 13
   and S14.ProductID = 14
order by 6 desc

those big honking IIFs decide whether the same supplier is supplying 12 and 13, or 12 and 14, or 13 and 14, or 12 and 13 and 14, and therefore decides which mail costs to add in

order by 6 sorts the whole shebang descending by totaltotal

the best combo comes up first, so you could actually add TOP 1 right after the SELECT and bob's your uncle

i hope you have the data to test this properly

if you want to go to 4 products instead of 3, you'll have to write that yourself

rudy

 

[r937]

whoops, sorry, that should be ORDER BY 6 ASC so the lowest overall total comes up first

rudy

 

[mymou]

Hi r937

Your PS Query is a great improvement on my generating separate queries for each product - but you still end up using a cartesian product (ie no joins).

For the mail cost I have used a DSum which is much simplier

total: [12].pricez+[13].pricez+[14].pricez+DSum("[Mail Cost]","Suppliers","SupplierID=" & [13].SupplierID & " or SupplierID= " & [14].SupplierID & " or SupplierID= " & [12].SupplierID)

and is very easy to add more (or less) products.

So we have methods that do solve the problem - but can improvements be made. eg If we had an order for 10 items with 6 suppliers possible for each product there would be 60,466,176 different combinations and would take some processing time. Is it possible to make things a little more efficient? So for example, the supplier with the highest mailing cost will never be the most efficient supplier for a single product (for the 6*10 example - this would limit the result by a considerable amount). I think this type of 'intelligent' thinking will greatly imporve the situation instead of relying on a cardisian product. Help.

Stew

 

[MichaelRed]

mymou, I have done a small ammount of programming in logistics support, and find even the soloution you do have to be less than complete. Of course situations differ quite a bit, but at least for the area I work in hte shipping costs vary greatly depending on the actual product and quantity of product. Further, different suppliers of the same product have different discount schedules, Some discount on the total value of orders, others discount on individual orders. Some discounts are applied to the order, while others are simply credited to an account. The system on which I have been working (struggling) also inlcudes different accounts with the same supplier, making the overall situation even more difficult, as the process needs to apply all of the 'rules' to seperate accounts and then track the results through combined accounts. The aproach used here is to build the 'business rules' into seperate tables and attempt to retrieve the applicable business rules from the account identifier.

Another consideration we have (which is far from the norm in wholesale-retail) is that generally, there is a fairly large volume of return merchandise in certaain categories. the 'business rules' for this merchandise vary between suppliers and must also be considered. Altogether, the issue of consolidation of purchases is at least daunting, and at worst not completly solveable through simple queries or lookups. If you use Excel, there is a 'solver' function which is derived from (linear) regression analysis. It might be of interest for you to at least look at the examples and try some subsets of your problems in that medium. If the experience in Excel is of interest to you after a bit of study and experimentation, you could approach your employer with the concept of getting some of the more powerful versions of the regresion tools (SPSS, MathLab, ...).

You are attempting the classic "maximize profit through minimizing expenses" which is taught at every bussiness school in the entire known world, so you could easily find a course on the overall subject at any local (business) college. You have a grasp of both the concept and the complexity of the issue, I suspect you also have sufficient math to do hte work, so all you appear to need is the formal grounding in the application of the concept throuigh the math.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[mymou]

Hi MichaelRed

Always a bit scary to get a reply from you - however helpful!

I think you can tell I am approaching this situation with caution. However, my solution is quite feasible for a small data sets - using the business rules at a later date - for every combination. As a plus, it is nice to see a carteian product doing something more than turning DBAs into rabid beasts. btw are there different forms of Cartesian Product?

Your input is invaluable (in fact, exactly what i was looking - a new approach). There is a Access add-on - Total Access Statistics (FMS)- any opinion? I would rather not get the stats books out (and reprogram the wheel)- they were a pain the first time around! I would rather continue to work within Access or VB - rather then enter the (unknown) world of SPSS and the like.

If you can show me any working examples, resourses, links etc - I will be more than greatful.

Stew

 

[MichaelRed]

Sorry to be the 'scary' one. I think (hope?) it is because I am just a bit more conservative than the average, but as the 'they' say, I am what I am ...

for substantive items, I have no opinion on the various stats packages available, including the ones I mentioned or 'FMS'. I -long away and far ago- needed most of the simplistics stats and could not afford the nicer packages available at the time, so I 'did the math' programmatically. Mine are STRICTLY home grown and not very suitable for general use, but I am familiar with their vageries and 'tinker' with them as necessary.

I am not at all sure I understand your question re 'different' cartesian products. Certainly the term is used differnetly in db land than in other diciplines, however I am only aware of the single (db land) deffinition.

As to 'working examples', I cannot supply any for a variety of reasons, the major one being that the program and the data are 'proprietry'.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over