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

I did something similar in the past. I hope the performance is sufficient for you.

<sql>
select
sum(qty * rate)
from (
select
iif (qty + qtyOut >= 15 /*current stock */, (15 /*current stock */ - qtyOut), qty) as qty,
rate
from (
select
qty,
rate,
coalesce (
(
select sum(PurchaseBillsInner.qty)
from PurchaseBills as PurchaseBillsInner
where (PurchaseBillsInner.dtd > PurchaseBills.dtd)
),
0
)as qtyOut
from PurchaseBills
) as PurchaseBillsIntermediate
where (qtyOut <= 15 /*current stock */)
) as PurchaseBillsFifo
</sql>


José Manuel Sanjuán.