Calculating Profit In Sproc

Hi I am quite struggling to work out how to calculate profit in stored procedure.


The problem is


I have the table:


share name / transaction date/ is it buy or sell / no of shares bought or sold /total cost





I need to calculate how much profit was made on sold shares


The problem is that I need to always assume that you sell shares which were bought first first.


So if I have in chronological order:


tesco buy 50 £200


tesco buy 40 £100


tesco sell 55 £600


profit is


costtobuy of 55 shares: 50 shares at £200 + 5 shares at (£100/40*5 =)£12.50 = £212.50


profit = £600 - £212.5 = £387.50 (too good to be true)








my plan was (if it is too confusing please ignore it and just read the part above)





1 to work out no of shares sold and total cost of sold shares


so


select sum(no of shares) as nosold ,sharename where transaction = sell and sharename= @sharename return noofshares


select sum(totalsold) as totalcostsold , sharename where transaction = sell and sharename = @sharename return totalsold


now i need to loop through buys to work out cost to buy of number of shares sold:





so i need to loop through a table which would have buy transactions only(share name, nobought, totalcost) and check what was the cost to buy of this number of shares:


@nosold(from above table)


@sharename





output totalcosttobuy = 0





if(nobought = nosold) totalcosttobuy = totalcostobuy + totalcost


retrun


else if (nobought>nosold)totalcosttobuy = totalcostobuy + (totalcost/nobought)*nosold


return


else if( nobought<nosold)totalcosttobuy = totalcostobuy + totalcost


nosold = nosold-nobought (go back to beginning of if)


where sharename = @sharename


group by sharename








Well I need this if statement to loop through the rows in the table how can I do that?


Then I can do profit = totalcosttosell - totalcosttobuy