On Thu, Mar 3, 2011 at 6:45 AM, 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
>
> Bhavbhuti

The way I tackle such problems is with select stored procedure for example:

-----------------
create procedure pricing(item_id integer) returns (last_price numeric(15,2)
as
begin

select rate
from bill
where
item_id = :item_id
order by bill_date desc
rows 1
into last_price;

-- Handle case where no items are sold.
last_price = iif(last_price is null, 0.00, last_price);
suspend;

end
-----------------------

Then we can do a join of the select procedure in a SQL query as follows:

-----------------------
select b.item_id, p.last_price * b.qty
from bill b
inner join pricing(b.item_id) p
-----------------------

This may not be exactly what you want but I home it points you in the right
direction.

Thomas Clarke.

>
>

--
Thomas Clarke
Cyber Sea Incorporated
#6 Kingston Terrace
Flint Hall
St. Michael
Barbados BB11070

voice: (246) 234-9692
email: tclarke@...
web: http://cybersea.biz


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