Hi!

You can try to use the custom collecting function for that, however, it will work ONLY in VFP, that means you have to change this when moving to SQL.

public giQueryStockID, giQueryStockSum
...
m.lnCurrentStockBalance = ...
Select StockID, InvNo, ItemNo, Qty, Cost, Date, Include(Cost, StockID, m.lnCurrentStockBalance) ;
  from LatestStockInDetails Where StockID=...

...

procedure Include
lparameters pnCost, piStockID, pnBalance
if giQueryStockID <> piStockID
  giQueryStockID = piStockID
  giQueryStockSum = Cost
else
  giQueryStockSum = giQueryStockSum + Cost
endif
return giQueryStockSum <= m.lnCurrentStockBalance

You can use above function also for larger select statement when you select all stocks with joined detail items, but do not forget to force the order of join statements so stocks re selected first than items are joined to them, otherwice function will work incorrectly.

Woow, got an idea at the last moment. Try following query:

select L.StockID, L.InvNo, L.ItemNo, L.Qty, L.Cost, L.Date, ;
    (NVL(sum(L2.Cost),0) + L.Cost <= m.lnCurrentStockBalance) as Include ;
  from LatestStockInDetails L
    left join LatestStockInDetails L2
    on L2.StockID=L.StockID AND L2.InvNo<L.InvNo
  Group By L.StockID, L.InvNo
  Where L.StockID=...

HTH.

>Hi,
> I am wiritng module to calculate item's cost using FIFO method. Therefore, I need to get all of the latest stock in details which it's qty can "included" in current item's balance qty. For example:
>

>Item A
>Current Stock Balance : 50
>
>Latest StockIn Details
>InvNo ItemNo   Qty   Cost Date
>-------------------------------
>A01   ItemA    10    10   22/07/2001   - Include
>A02   ItemA    10    20   21/07/2001   - Include
>A03   ItemA    10    15   19/07/2001   - Include
>A04   ItemA    10    10   18/07/2001   - Include
>A05   ItemA    10    10   10/07/2001   - Include
>A06   ItemA    10    10   08/07/2001   - Exclude since the total qty exceed 50
>
>

>How to use SQL to get the "included" stock in details for all of the items ??
>
>Thank you