On 05/03/2011 04:58, JackR wrote:

>
> First off, since you seem to be looking for the last four shipments,
> rather than the first four, it would seem that you are looking for a
> LIFO (Last In First Out) accounting rather than FIFO.
>

Thanks Jack. I really appreciate this CTE example. I adapted your code
to (my) real life as below (my first on extending the CTE, Svein are you
listening? Thanks. :) ).

One thing I would like to do to this is, know the qty. to calculate
against each row returned, esp. the old record, which will be at the
bottom of the result set, cause this will be the balance qty. and the
bQty o2 b2Qty do not necessarily match. So it is possible to have the
qty as setoff against that particular record returned?

The other practical problem I will have to manage to yours and Jose's
suggestions is interfacing it with the real life data set of current
balance.

Please advise

Thanks and regards
Bhavbhuti

WITH INVENTORY AS
(
SELECT t.iID, t.tDt, s.iItemID, s.bQty, s.bRate
FROM tPurchaseBill t
JOIN sPurchaseBillItem s
ON s.iPID = t.iID
WHERE s.iItemID = 9 /* item id */
)
, LIFO AS
(
SELECT i.iID, i.tDt, i.iItemID, i.bQty, i.bRate, SUM(i2.bQty) AS b2Qty
FROM INVENTORY i
INNER JOIN INVENTORY i2
ON i2.tDt >= i.tDt
GROUP BY i.iID, i.tDt, i.iItemID, i.bQty, i.bRate
ORDER BY i.tDt DESC
)

SELECT *
FROM LIFO l
WHERE l.b2Qty <= (SELECT MIN(b2Qty)
FROM LIFO
WHERE b2Qty >= 76 /* current stock */)