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
