On Fri, Mar 01, 2002 at 02:53:20AM +0000,
John <john@localhost.localdomain> wrote:
>
> How do I get SQL to do this. I tried using a CASE statement. If
> quantity > 0, then use the SELECT statement above. If quantity < 0,
> then find the average cost using all transactions from the date before
> the date of the current transaction and then multiply that by the
> negative quantity. That didn't give me the right number. Does anyone
> have any suggestions? Assume there will be multiple purchases and
> sales for each security tracked by the database.
If you have buy and sell transactions look the same except for sign
then you should be able to just "sum" things up.
You either want to calculate a price per share for each transaction
you do which is always positive (or perhaps easier with commisions,
to track the cost of the transaction as a signed number) and the number
shares transferred as a signed number.
