diff options
| -rw-r--r-- | sql/dawn.sql | 26 |
1 files changed, 13 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 890492b2..ee78fe2d 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -39,7 +39,7 @@ CREATE TABLE bonds(id serial primary key, folder bond_strat, custodian varchar(12), cashaccount varchar(10), - cp_code varchar(12) references counterparties(code), + cp_code varchar(12) REFERENCES counterparties(code), trade_date date, settle_date date, cusip varchar(9), @@ -54,7 +54,7 @@ CREATE TABLE bonds(id serial primary key, accrued_payment float); CREATE TABLE position(date date, - identifier varchar(12) REFERENCES position_indicative(identifier) + identifier varchar(12) REFERENCES securities(identifier) notional float, coupon float, factor float, @@ -115,31 +115,31 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_positions(p_date date, p_class asset_class DEFAULT NULL, include_unsettled boolean DEFAULT True) -RETURNS TABLE(identifier varchar(12), description varchar(32), strategy bond_strat, - last_settle_date date, notional float, bbg_type bbg_type) AS $$ +RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, + last_settle_date date, principal_payment float, accrued_payment float, bbg_type bbg_type) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; DECLARE unsettled_opt text; BEGIN IF p_class is not NULL THEN - asset_opt := 'and position_indicative.asset_class=$2 '; + asset_opt := 'and securities.asset_class=$2 '; ELSE asset_opt := ''; END IF; IF include_unsettled THEN - unsettled_opt = 'or settle_date<=$1'; + unsettled_opt = 'or settle_date>=$1'; ELSE unsettled_opt = ''; END IF; - sqlquery := 'WITH temp as (SELECT bonds.identifier, bonds.description, asset_class, settle_date, folder, - sum(faceamount*(2*buysell::int-1)) - OVER (PARTITION by bonds.identifier) notional from bonds where trade_date<=$1) - SELECT DISTINCT ON (temp.identifier) temp.identifier, temp.description, folder, settle_date, notional, - securities.bbg_type + sqlquery := 'WITH temp as (SELECT bonds.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) + SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, + settle_date, temp.principal_payment, temp.accrued_payment, securities.bbg_type FROM temp LEFT JOIN securities USING (identifier) - WHERE (temp.notional>0 '||unsettled_opt||') and paid_down>$1 '||asset_opt - ||' order by identifier, settle_date desc'; + WHERE (temp.notional>0 '||unsettled_opt||') AND paid_down>$1 '||asset_opt + ||' ORDER BY identifier, settle_date desc'; RETURN QUERY EXECUTE sqlquery USING p_date, p_class; END; $$ LANGUAGE plpgsql; |