How to limit selection of records

[henio]

Hi all,

I have two tables: order lines and stock

The Order lines table contains

Customer Code
Product Code
Qty ordered

The Stock table contains

Product Code
Available qty

I need to extract orderlines up to the available qty of stock for each product. Can this be done without resorting to VBA?

Cheers,
Henio

 

[Golom]

Yes but it's a bit ugly.

SELECT I.ProductID, O1.QtyOrdered, 

IIF ( O1.QtyOrdered <= (I.InStock - nz((Select Sum(QtyOrdered) From Ordered X 
WHERE X.ProductID = I.ProductID
AND X.QtyOrdered < O1.QtyOrdered))), 

O1.QtyOrdered,

(O1.QtyOrdered + (I.InStock - nz((Select Sum(QtyOrdered) From Ordered X 
WHERE X.ProductID = I.ProductID
AND X.QtyOrdered <= O1.QtyOrdered))))) As [Available]

FROM Ordered AS O1 INNER JOIN InStock AS I ON O1.ProductID = I.ProductID
GROUP BY I.ProductID, O1.QtyOrdered, I.InStock

HAVING (I.InStock - nz((Select Sum(QtyOrdered) From Ordered X 
WHERE X.ProductID = I.ProductID
AND X.QtyOrdered < O1.QtyOrdered))) > 0

ORDER BY I.ProductID, O1.QtyOrdered;

The biggest problem (and what most of this query is about) is handling the situation where you can fill some but not all of an order (e.g. 10 were ordered but there are only 6 left in stock.)

In this we are just filling the smallest orders first with the clause &quot;X.QtyOrdered < O1.QtyOrdered&quot;. If you wanted to fill orders by order date (for example) then change the criteria to &quot;X.OrderDate < O1.OrderDate&quot;