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
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