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.

I built a table following the structure suggested by your question:
CREATE TABLE INVENTORY EXTERNAL 'INVENTORY' (
HKEY D_ID /* D_ID = NUMERIC(15,0) NOT NULL */,
DTDATE DATE,
IQUANTITY INTEGER,
CRATE D_CURRENCY /* D_CURRENCY = NUMERIC(15,4) */
);
I put this code into IBExpert:
WITH LIFO AS
(
SELECT i.hKey, i.dtDate, i.iQuantity, i.cRate, SUM(i2.iQuantity) iQty
FROM INVENTORY i
INNER JOIN INVENTORY i2 ON i2.dtDate >= i.dtDate
GROUP BY i.hKey, i.dtDate, i.iQuantity, i.cRate
ORDER BY dtDate DESC
)

SELECT l.hKey, l.dtDate, l.iQty, l.cRate
FROM LIFO l
WHERE l.iQty <= (SELECT MIN(iQty)
FROM LIFO
WHERE iQty >= 15)
I got these results:
HKEY DTDATE IQTY CRATE
567 2/20/2011 6 6.95
456 2/10/2011 11 6.85
345 2/5/2011 14 5.75
234 1/15/2011 21 5.2

--- In firebird-support@yahoogroups.com, José Manuel
<jsanjuandeive@...> wrote:

>
>
>
>
>
>
>
>
> --- In firebird-support@yahoogroups.com, "venussoftop" venussoftop@

wrote:

> >
> > Hi all
> >
> > Is this possible in SQL. I have an items list with current stock.

I just want to pull the last so many purchase bill item rows as will
satisfy the current stock qty.

> >
> > I mean
> > ItemA current stock qty 15
> >
> > Joined Purchase Bill Item rows
> > no. 123 dtd. 01/01/2011 qty. 5 rate 5.5
> > no. 234 dtd. 15/01/2011 qty. 7 rate 5.2
> > no. 345 dtd. 05/02/2011 qty. 3 rate 5.75
> > no. 456 dtd. 10/02/2011 qty. 5 rate 6.85
> > no. 567 dtd. 20/02/2011 qty. 6 rate 6.95
> >
> > I would like to see the bills, 234, 345, 456, 567 as they encompass

the current stock of 15 (though the total of these are 21)

> >
> > The reason for this is that there may be many purchase bills over

the years for this same ItemA but only the last of them are required to
calculate the valuation, 6.95*6 + 6.85*5 + 5.75*3 + 5.2*1 = 98.4

> >
> > Please advise
> >
> > Thanks and regards
> > Bhavbhuti
> >
>

[Non-text portions of this message have been removed]