Im not to certain what you are trying to do, but I suspect its some form of accountancy valuation rule, probably FIFO
first off you need to know what the 'last' record is.
unless you always buy and sell on the same transaction then you need to identify what is the last price of each item. you should be able to do that using sub queries
so thats going to look something like:-
you would be better off having another table called, say Products, and use the PK of that table in your transaction table. mind you you'd be better off separating the sales and purchasing transaction but thats another story
SELECT Robert_Bob.ProductID,sum( Robert_Bob.salesQty) as TotSales, sum(Robert_Bob.PurchaseQty) as TotPurchase, (select top 1 SalesPrice
from robert_bob as SP
where sp.productid = robert_bob.productID and sp.salesprice>0
order by id DESC) AS SPrice, (select top 1 PurchasePrice
from robert_bob as PP
where Pp.productid = robert_bob.productID and Pp.purchaseprice>0
order by id DESC) AS PPrice
group by productId;
Last edited by healdem; 12-13-13 at 05:01.
I'd rather be riding on the Tiger 800 or the Norton