A SELECT statement doing this is interesting. Just curious what would happen if the closing stock consists of 2 purchases, ie. purchase of 10 @ 12.50 and another purchase of 10 @ 13.00. Out of which only 5 are sold @ whatever rate. Now the closing stock is 15 out of which 5 is @ 12.50 and 10 is @ 13.00.

>>And if the item never was purchased - latest from the stock? What if you update your stock from other source (e.g. item in stock has later date when purchased item)?
>>
>>
>>Then Openinge rate will be use
>
>Try:
>
>

>
>SELECT O.Codes as [Code], O.Products, ;
>OpeningQty as [OpeningQty], ;
>PurchaseQty as [PurchaseQty], ;
>PurchaseAmount as [PurchaseAmount], ;
>OpeningQty + NVL(PurchaseQty,0) as [TotalQty], ;
>OpeningAmount + NVL(PurchaseAmount,0) as [TotalAmount], ;
>NVL(SoldQty,0) as [SoldQty], ;
>NVL(SoldAmount,0) as [SoldAmount], ;
>OpeningQty + NVL(PurchaseQty,0) - NVL(SoldQty,0) as [ClosingQty], ;
>(OpeningQty + NVL(PurchaseQty,0) - NVL(SoldQty,0)) * IIF(LastStocked > NVL(LastPurchased,{}),LastStockRate, LastPurchaseRate) as [ClosingAmount] ;
> FROM (select Codes, Products, SUM(qty) as OpeningQty, ;
>SUM(Amount) as OpeningAmount, MAX(dates) as LastStocked from Open_Stock group by Codes, Products) O ;
>INNER JOIN (select Rate as LastStockRate, Codes, Dates FROM Open_Stock) O1 ON O.Codes = O1.Codes AND O.LastStocked = O1.Dates ;
>LEFT JOIN (select Codes, Products, ;
>SUM(qty) as PurchaseQty, SUM(Amount)  as PurchaseAmount, MAX(Dates) as LastPurchased ;
>from Purchase group by Codes, Products) P ;
>ON O.Codes = P.Codes ;
>LEFT JOIN (select Rate as LastPurchaseRate, Codes, Dates FROM Purchase) P1 on P.Codes = P1.Codes and P.LastPurchased = P1.Dates ;
>LEFT JOIN (select Codes, Products, ;
>SUM(qty) as SoldQty, SUM(Amount) as SoldAmount ;
>from Sales group by Codes, Products) S ;
>ON O.Codes = S.Codes
>