Den 07.04.2016 08:42, skrev venussof...@gmail.com [firebird-support]:
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
