>

>openingamount + nvl(purchaseamount,0) - nvl(soldamount,0) as [ClosingAmount] ;
>

>
>Nomi, thanks for helping but I want to draw you attention to above line of codes
>
>the closing qty will multiply by purchase rate
>
>The last purchase rate of 1401 is 14.775
>so closing amount of 1401=?
>
>closing amount=closing qty*last purchase rate
>14.775*6=88.65
>
>so closing amount of 1401=88.65
>
>But your query displays 59.70

I assumed that Closing Amount is the difference between amounts. If no, we want to get Closing Qty and multply by the latest rate. If so, the problem becomes a bit more complex. Do you need one select statement or multiple select statements are OK?

In other words, do you want to apply the latest rate for the amounts or you want to use the rates at the moment of purchase? If so, how will you know which exact items you sold? You match them by rate then?

If that's the case, I'll need to re-write the query and may be we need to use scan based solution here instead.