On 04/03/2011 06:06, José Manuel wrote:

>
>
> I did something similar in the past. I hope the performance is
> sufficient for you.
> < chomp >
> José Manuel Sanjuán.
>

Amazing what can just SQL do!!!! I had expected all the people to only
suggest SPs.

Thanks a lot José. Below is adapted to my real life:

Kind regards
Bhavbhuti


SELECT SUM(qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= 30 /*current stock */
, (30 /*current stock */ - qtyOut)
, bQty) as qty
, bRate
FROM (SELECT sPurchaseBillItem.bQty
, sPurchaseBillItem.bRate
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
FROM sPurchaseBillItem AS
PurchaseBillItemInner
JOIN tPurchaseBill AS
PurchaseBillInner
ON
PurchaseBillInner.iID = PurchaseBillItemInner.iPID
WHERE (PurchaseBillInner.tDt >
tPurchaseBill.tDt AND PurchaseBillItemInner.iItemID = 9 /* item id */))
, 0)as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = 9 /*
item id */) AS PurchaseBillsIntermediate
WHERE (qtyOut <= 30 /*current stock */)) AS PurchaseBillsFIFO