diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 10 |
1 files changed, 8 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 9ba4d0c9..87ad9604 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -791,6 +791,12 @@ WHERE date <= p_date ORDER BY identifier, date DESC) b USING (identifier) END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE VIEW bonds_trades AS +(SELECT identifier, asset_class, trade_date, settle_date, buysell, price, folder, + principal_payment, accrued_payment, notional AS faceamount, fund +FROM bonds LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid +LEFT JOIN accounts USING (code)); + CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, include_unsettled boolean DEFAULT True, @@ -814,9 +820,9 @@ BEGIN ELSE unsettled_opt = ''; END IF; - sqlquery := 'WITH temp AS (SELECT bonds.identifier, asset_class, settle_date, folder, + sqlquery := 'WITH temp AS (SELECT bonds_trades.identifier, asset_class, settle_date, folder, principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) - OVER (PARTITION BY bonds.identifier) notional FROM bonds WHERE trade_date<=$1 + OVER (PARTITION BY bonds_trades.identifier) notional FROM bonds_trades WHERE trade_date<=$1 AND fund=$3) SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, securities.coupon, start_accrued_date, settle_date, temp.principal_payment, |