Hi all

Sorry for resurrecting this very old thread but I have come across a situation (hopefully Jose would be able to answer).  I have found a bug in the bQty column it was not able to reduce itself to the current stock, eg. 3, and still it returns two row with a qty of 3+2 = 5 qty is valued which is wrong

SELECT qty, bRate, (qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= 3 /*current stock */
, (3 /*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 = 342 /* item id */))
, 0) as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = 342 /*item id */) AS PurchaseBillsIntermediate
WHERE (qtyOut <= 3 /*current stock */)) AS PurchaseBillsFIFO

returns
3.000000    67.190000    201.570000
2.000000    67.190000    134.380000

the expected would have been
1.000000    67.190000    67.190000
2.000000    67.190000    134.380000

the original bqty in the top row is 11 but now only 1 of it remains, yet the above.  The only reason this might be happening is that both the rows belongs to the same tPurchaseBill parent record, because the same purchase bill had stock coming in from different medicine batches, thus the user needed to enter the same item in the same purchase bill twice but with different qty and batch nos.

Please advise.

Thanks and regards
Bhavbhuti

Hi Bhavbhuti!

I don't quite understand, could you show us the rows (only fields mentioned in the query) of tPurchaseBill and sPurchaseBillItem? Then we should at least be able to reproduce your incorrect result, and hopefully think of a different way to obtain the result you want...

Set