Selection on price, stock and delivery time in one query

VDJ

New member
Local time
Today, 03:20
Joined
Sep 2, 2013
Messages
6
Hello,

I have a table with following structure :
Fabcode Price Stock Date Supplier

Fabcode : the unique code of the article
Price : the price by this supplier
Stock : quantities for the moment in stock by this supplier
Date : When there is no stock at this supplier, the estimated time of arrival
Supplier : name of the supplier

A selection for looking the lowest price for a supplier who has stock is no problem. But I want also the fastest delivery date when no one has stock.

Example

Fabcode Price Stock Date Supplier
Product1 5 3 A
Product1 6 4 B
Product1 7 5 C
Product1 8 6 D
Product2 14 73 A
Product2 12 56 B
Product2 15 14 C
Product3 30 0 30/12/13 A
Product3 24 0 B
Product3 25 0 26/12/13 C
Product3 32 0 26/12/13 D

Result :
Product1 5 3 A (because supplier A has stock and the lowest price)
Product2 12 56 B (because supplier B has stock and the lowest price)
Product3 25 0 26/12/13 C (because no one has stock, but supplier C has the shortest delivery time AND the lowest price)

Remark 1 : when there is no delivery date (and no stock), this supplier should be ignored for that product
Remark 2 : when no one has stock, the delivery date is priority, when 2 supplies has the same delivery date than the price is priority.

Can this in one query or SQL-statement ?

Thanks in advance.

Johan
 
You can use a subquery in your criteria which would be something like:

DelDate=(SELECT Min(DelDate) FROM myTable as tmp WHERE FabCode=myTable.FabCode AND Stock=0)
 
What happens when a supplier changes the price of a Product? How do you handle that in 1 table?

My first reaction is that you have not normalized your data, but we don't know your business or business rules.
 
First, [Date] is a bad name for a field because its a reserved word and makes coding more difficult. I recommend renaming that field by prefixing with what Date it represents (InStockDate).

Also, because you didn't provide your datasource name, I used 'YourTableNameHere' as the name of the datasource to build the queries you are going to need to accomplish this. Replace all instances of 'YourTableNameHere' with the name of your datasource that you posted examples from.

My solution takes 3 sub-queries:

Code:
SELECT YourTableNameHere.Fabcode, Min(YourTableNameHere.Price) AS LowestPrice
FROM YourTableNameHere
WHERE (((YourTableNameHere.Stock)>0))
GROUP BY YourTableNameHere.Fabcode;

Name the above query 'sub_BestSupplier_InStock'. It gets the lowest price for those suppliers with the product in stock.

Code:
SELECT YourTableNameHere.Fabcode, Min(YourTableNameHere.Date) AS EarliestDate
FROM YourTableNameHere LEFT JOIN sub_BestSupplier_InStock ON YourTableNameHere.Fabcode = sub_BestSupplier_InStock.Fabcode
WHERE (((sub_BestSupplier_InStock.Fabcode) Is Null))
GROUP BY YourTableNameHere.Fabcode;

Name the above query 'sub_BestSupplier_OutStock_1'. It determines the next time a supplier will have a product where that product is out of stock everywhere.

Code:
SELECT sub_BestSupplier_OutStock_1.Fabcode, sub_BestSupplier_OutStock_1.EarliestDate, Min(YourTableNameHere.Price) AS LowestPrice
FROM sub_BestSupplier_OutStock_1 INNER JOIN YourTableNameHere ON (sub_BestSupplier_OutStock_1.Fabcode = YourTableNameHere.Fabcode) AND (sub_BestSupplier_OutStock_1.EarliestDate = YourTableNameHere.Date)
GROUP BY sub_BestSupplier_OutStock_1.Fabcode, sub_BestSupplier_OutStock_1.EarliestDate;

Name the above query 'sub_BestSupplier_OutStock_2'. It gets the lowest price of items that are out of stock everywhere, but with the same in-stock date.

Lastly, this is the money-query which will give you what you want:

Code:
SELECT YourTableNameHere.*
FROM YourTableNameHere INNER JOIN sub_BestSupplier_InStock ON (YourTableNameHere.Price = sub_BestSupplier_InStock.LowestPrice) AND (YourTableNameHere.Fabcode = sub_BestSupplier_InStock.Fabcode)

UNION ALL SELECT YourTableNameHere.*
FROM YourTableNameHere INNER JOIN sub_BestSupplier_OutStock_2 ON (YourTableNameHere.Price = sub_BestSupplier_OutStock_2.LowestPrice) AND (YourTableNameHere.Date = sub_BestSupplier_OutStock_2.EarliestDate) AND (YourTableNameHere.Fabcode = sub_BestSupplier_OutStock_2.Fabcode);
 
What happens when a supplier changes the price of a Product? How do you handle that in 1 table?

My first reaction is that you have not normalized your data, but we don't know your business or business rules.

This table is daily generated with new data from our suppliers.

Johan
 
First, [Date] is a bad name for a field because its a reserved word and makes coding more difficult. I recommend renaming that field by prefixing with what Date it represents (InStockDate).

Hello,

Thank you for the quick response.
My field names are different (and also in another language), but I made them shorter for the example.
After converting to the correct tables and field names, everything looks fine.
I will check of there are no errors, but at first sight it looks very good.

Thanks again !

Johan
 
You can use a subquery in your criteria which would be something like:

Thank you for the answer.
I' ve tried the solution of plog, but later on I will also try your solution.
In the future I will see wich is the fastest or the most stable.

Johan
 

Users who are viewing this thread

Back
Top Bottom