diff options
| -rw-r--r-- | sql/dawn.sql | 35 |
1 files changed, 26 insertions, 9 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index d5f49c30..783f1f66 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -795,15 +795,32 @@ WHERE date <= p_date ORDER BY identifier, date DESC) b USING (identifier) END; $$ LANGUAGE plpgsql; -CREATE VIEW bond_trades AS -(SELECT dealid, tradeid, bond_allocation.id AS id, identifier, description, asset_class, trade_date, settle_date, buysell, price, accrued, folder, - principal_payment * notional/sum(notional) OVER w AS principal_payment, accrued_payment* notional/sum(notional) OVER w AS accrued_payment, (principal_payment + accrued_payment) * notional/sum(notional) OVER w AS net_amount, - current_face * notional/sum(notional) OVER w AS current_face , notional AS faceamount, fund, counterparties.code AS cp_code, counterparties.name AS counterparty -FROM bonds LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid -LEFT JOIN accounts USING (code) -LEFT JOIN counterparties ON bonds.cp_code=counterparties.code -WINDOW w AS (PARTITION BY tradeid)) -; +CREATE OR REPLACE VIEW bond_trades +AS SELECT bonds.dealid, + bond_allocation.tradeid, + bond_allocation.id, + bonds.identifier, + bonds.description, + bonds.asset_class, + bonds.trade_date, + bonds.settle_date, + bonds.buysell, + bonds.price, + bonds.accrued, + bonds.folder, + bonds.principal_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS principal_payment, + bonds.accrued_payment * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS accrued_payment, + (bonds.principal_payment + bonds.accrued_payment) * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS net_amount, + bonds.current_face * bond_allocation.notional / sum(bond_allocation.notional) OVER w AS current_face, + bond_allocation.notional AS faceamount, + accounts.fund, + counterparties.code AS cp_code, + counterparties.name AS counterparty + FROM bonds + LEFT JOIN bond_allocation ON bonds.id = bond_allocation.tradeid + LEFT JOIN accounts USING (code) + LEFT JOIN counterparties ON bonds.cp_code = counterparties.code + WINDOW w AS (PARTITION BY bond_allocation.tradeid); CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, |