I have created a PostgreSQL database to keep track of my investments.
I have created one table to track my purchases and sales of
securities. One piece of information I need to be able to extract
from the database is the cost of each security. Calculating the cost
of each security if I have only purchased that security is easy:
SELECT quantity,price,quantity*price AS cost
FROM transactions;
But what if I have bought *and* sold the security? Let's say I bought
300 iShares on December 15, 2001 at $125 per share, 500 iShares on
January 1, 2002 at $135 per share, and then I sold 100 iShares on
February 15, 2002 at $110 per share. I can calculate my cost by hand
easily enough, and the result would like the following:
Quan Price Cost
==== ===== ====
300 125 37,500
500 135 67,500
(100) 110 (13,125)
----- --------
700 91,875
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.
