Correlated query to find cheapest manufactuer

[rafnews]

Hi,



I have a SQL Query that returns me:
- the "product code" i must purchase,
- the "quantity" to purchase for this product
- and if it's in "promotion or not".

Based on this result, i must to look at "manufacturer" table and find the cheapest "price" for each product to purchase and the quantity available at the manufacturer.
If "quantity to order" (e.g. 20) is higher than "quantity available" (e.g. 12) at "cheapest manufacturer", i must order the missing quantity (8) to the next cheapest manufacturer.
How can i do that ?

thx

[ranman256]

I see 1 query to pull the order list
qry2 that does a min price on the suppliers linked to Q1, where QTY => Q1.qty

this still may have holes using the Qty restriction.

[rafnews]

Ok so to make you understand a little bit better.

Here is the result of my previous Query: http://prntscr.com/6m0hin
so you can see what are the product code to order, their respective quantity and if they are promotional or not.

and this is the supplier/manufacturer prod table
http://prntscr.com/6m0i85

for each product i need to purchase:
1. if product i not promotional, i must order the full quantity to the cheapest manufacturer. So in my case for MON003, i must order 16 to IB for $225
2. for promotional products, i must firstly check if the cheapest manufacturer has enough prod in stock. If not, i order all the prod in stock the manufacturer has, and i must order to missing ones to the next cheapest manufacturer.
So in my case for DTP1002 i must order 5 prod to HP for $700 and next 15 prod to IB for $800

this is a business rule that gives me headache

[orange]

What exactly have you tried so far?
show us your query sql and any vba you have.

[rafnews]

In fact it's an exercise. here it's the text:
Procurement manager, Bob, wants to generate procurement orders for the suppliers.
These procurement orders will be based on the available data and the business rules.
Upon further investigations, we have found out the complete set of business rules:
Business Rules
● Only those products need to be ordered where the PROD_QOH (quantity on
hand) will fall below the PROD_MIN_QOH (minimum quantity on hand) after
providing for the orders with the ‘ON ORDER’ status.
● Procurement order quantity should be such that the final stock is equal to the
PROD_MAX_QOH (maximum quantity on hand)
● Products should be ordered from the supplier with the lowest price for any
product not on promotion.
● If the lowest price supplier cannot provide the required quantity for a product on
promotion, the remaining amount should be ordered from the next best supplier.
● Requested Date should exceed the order date by 10 days
Here are the required steps
1. SM1: Write a query to determine the products which need to be procured and
the corresponding procurement quantity for each product. Results should
include the Product Code, Required Quantity for each product (zero or more)
and whether the product is a promotional product. (7 points)
2. SM2: Write a query to determine the lowest procurement cost for each
product. Results should include the Product Code, Lowest Cost for each
product. (5 points)
3. SM3: For each of the products to be procured, find the procurement order
details: product code, supplier(s) code, order quantity and unit cost. (You can
split this in multiple queries) (12 points)

I already did SM1, SM2 and now i'm trying to solve SM3.

[ranman256]

yeah, this may require many queries
1 set for promotion
1 set for non promo.

I myself would stick in some code the run thru the list checking both promo or not, cheapist or not, qty or not. But Im not sure of your coding skills. BUT I think it can be done with queries only, like i stated, 1 set for each phase.
Maybe even putting candidates into a 'working' table, then deleting those that dont fit.

[orange]

Can you show us the table(s) design; some sample data, and your solutions for SM1 and SM2?

[rafnews]

the DB URL schema is available on http://prntscr.com/6m12ki

my SM1 query:

SELECT sub1.[Product Code], (product.prod_max_qoh-(product.prod_qoh-sub1.[qty_ordered])) AS [Quantity To Order], IIf(product.prod_promo="1","Yes","") AS [Promotional Product]
FROM (SELECT order_line.prod_code AS [Product Code], Sum(order_line.units) AS qty_ordered FROM [order], order_line WHERE [order].status='OnOrder'    AND       order_line.ol_ordernum = [order].ordernum GROUP BY order_line.prod_code)  AS sub1, product
WHERE ( ((product.prod_qoh-sub1.[qty_ordered]) < product.prod_min_qoh) AND (sub1.[Product Code]=product.prod_code));

my SM2 query:

SELECT product.prod_code AS [Product Code], subLP.[lower_price] AS [Lowest Price]
FROM product, (SELECT s_prod_code, min(prod_cost) AS lower_price FROM supplier_prod GROUP BY s_prod_code)  AS subLP
WHERE product.prod_code = subLP.s_prod_code;

[orange]

cross posted at this site