Den 07.04.2016 08:42, skrev venussoftop@... [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

......
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

Thanks for responding Set.  Please do let me know if you have a replacement CTE which might be more efficient than what I have below, I have always admired your CTEs (afraid I still can't program a new CTE myself :( ).

I have since posting been looking into this issue.  It seems it was a many fold issue.  The original query assumed that each purchase of the item is on different days, that was the source of failure and because this example case was that the same item was purchased in the same purchase bill highlighted the issue.


My table data of important and related fields is below:
INSERT INTO TPURCHASEBILL (IID, CBK, INO, TDT) VALUES ('446', '     ', '357', '27.02.2016, 00:00:00.000');

INSERT INTO SPURCHASEBILLITEM (IID, IPID, ISRNO, IITEMID, BQTY, BRATE, BAMT) VALUES ('753', '446', '1', '342', '11.000000', '67.190000', '671.900000');
INSERT INTO SPURCHASEBILLITEM (IID, IPID, ISRNO, IITEMID, BQTY, BRATE, BAMT) VALUES ('686', '446', '2', '342', '2.000000', '67.190000', '134.380000');

Where iID are the PKs for both the tables.  iPID in sPurchaseBillItem is the Foreign Key to tPurchaseBill table


My corrected full query is below my signature.  So what I had to do was first check if the date of the purchase bill was greater than in the inner query.  In case if the dates equaled then I had to check for the purchase bill no. being greater, even if that is equal as in my case, then I check for the Serial No. of the item in the same purchase bill to be greater.  The trick portion is this:

                                , COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
                                                FROM sPurchaseBillItem AS PurchaseBillItemInner
                                                    JOIN tPurchaseBill AS PurchaseBillInner
                                                        ON PurchaseBillInner.iID = PurchaseBillItemInner.iPID
                                                WHERE PurchaseBillItemInner.iItemID = 342 /* item id */
                                                    AND ((PurchaseBillInner.tDt > tPurchaseBill.tDt)
                                                        OR (PurchaseBillInner.tDt = tPurchaseBill.tDt
                                                                AND PurchaseBillInner.iNo > tPurchaseBill.cBk || CAST(tPurchaseBill.iNo AS CHAR(10)))
                                                        OR (PurchaseBillInner.tDt = tPurchaseBill.tDt
                                                                AND PurchaseBillInner.cBk || CAST(PurchaseBillInner.iNo AS CHAR(10)) = tPurchaseBill.cBk || CAST(tPurchaseBill.iNo AS CHAR(10))
                                                                AND PurchaseBillItemInner.iSrNo > sPurchaseBillItem.iSrNo))), 0) as bQtyOut

Thanks for your response Set

Kind regards
Bhavbhuti


SELECT itPBID
        , ctPBBk
        , itPBNo
        , ttPBDt
        , isPBIID
        , bValQty
        , bValRate
        , (bValQty * bValRate) AS bValuation
    FROM (SELECT itPBID
                    , ctPBBk
                    , itPBNo
                    , ttPBDt
                    , isPBIID
                    , IIF(bQty + bQtyOut >= 3 /*current stock */
                        , (3 /*current stock */ - bQtyOut)
                        , bQty) as bValQty
                    , bRate AS bValRate
                FROM (SELECT tPurchaseBill.iID AS itPBID
                                , tPurchaseBill.cBk AS ctPBBk
                                , tPurchaseBill.iNo AS itPBNo
                                , tPurchaseBill.tDt AS ttPBDt
                                , sPurchaseBillItem.iID AS isPBIID
                                , sPurchaseBillItem.bQty
                                , sPurchaseBillItem.bRate
                                , COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
                                                FROM sPurchaseBillItem AS PurchaseBillItemInner
                                                    JOIN tPurchaseBill AS PurchaseBillInner
                                                        ON PurchaseBillInner.iID = PurchaseBillItemInner.iPID
                                                WHERE PurchaseBillItemInner.iItemID = 342 /* item id */
                                                    AND ((PurchaseBillInner.tDt > tPurchaseBill.tDt)
                                                        OR (PurchaseBillInner.tDt = tPurchaseBill.tDt
                                                                AND PurchaseBillInner.iNo > tPurchaseBill.cBk || CAST(tPurchaseBill.iNo AS CHAR(10)))
                                                        OR (PurchaseBillInner.tDt = tPurchaseBill.tDt
                                                                AND PurchaseBillInner.cBk || CAST(PurchaseBillInner.iNo AS CHAR(10)) = tPurchaseBill.cBk || CAST(tPurchaseBill.iNo AS CHAR(10))
                                                                AND PurchaseBillItemInner.iSrNo > sPurchaseBillItem.iSrNo))), 0) as bQtyOut
                            FROM sPurchaseBillItem
                                JOIN tPurchaseBill
                                    ON tPurchaseBill.iID = sPurchaseBillItem.iPID
                                        AND sPurchaseBillItem.iItemID = 342 /* item id */
                            ORDER BY tPurchaseBill.tDt DESC
                                , tPurchaseBill.cBk
                                , tPurchaseBill.iNo) AS PurchaseBillsIntermediate
                WHERE (bQtyOut <= 3 /*current stock */ ))