Basic SQL Portfolio Report

[P. Croque]

OK...I've been playing around with select queries on the SQL backend
(gnucash 2.3.15, postgresql 8.4, ubuntu 10.04). My goal is to make an
SQL query (or set of queries) that gives a money-weighted portfolio
performance report (including dividends) for any given time period.

But I'm just learning SQL, so I'm only in the first stages. I've managed
to come up with a query that gives the current totals and values for
each security in the portfolio. At this point, it just gives the value
in the security's native currency based on the most recent quote (I
think).

Maybe someone can make use of it or improve on it, so I'm posting it
below.

----------------------------
SELECT 
  *, 
  ROUND((latest_quote*shares),2) AS value 
    FROM
      (SELECT 
        commodities.mnemonic AS commodity,
        ROUND(SUM(splits.quantity_num::decimal/splits.quantity_denom),4)
           AS shares,
        ROUND((latest_quote.value_num::decimal/latest_quote.value_denom),2)
           AS latest_quote
       FROM splits 
        JOIN transactions ON (splits.tx_guid = transactions.guid)
        JOIN accounts ON (splits.account_guid = accounts.guid)
        JOIN commodities ON (accounts.commodity_guid = commodities.guid)
        JOIN (SELECT t.* FROM
               (SELECT
                 commodity_guid,
                 MAX(date) AS maxdate 
                FROM prices
                GROUP BY commodity_guid) x
                  JOIN prices t ON (x.commodity_guid = t.commodity_guid)
                    AND (x.maxdate = t.date)) latest_quote
        ON (latest_quote.commodity_guid = commodities.guid)         
        WHERE    
          accounts.account_type = 'STOCK' OR
          accounts.account_type = 'MUTUAL'
        GROUP BY 
          commodities.mnemonic,
          latest_quote           
        ORDER BY 
          commodities.mnemonic) base_table;