diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 34 |
1 files changed, 19 insertions, 15 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 69041b5b..dc64b86f 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1093,30 +1093,34 @@ RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, s bbg_type bbg_type, figi varchar(12)) AS $$ DECLARE sqlquery text; DECLARE asset_opt text; -DECLARE unsettled_opt text; +DECLARE trade_or_settle text; BEGIN IF p_class is not NULL THEN - asset_opt := 'and securities.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'; + trade_or_settle = 'trade_date'; ELSE - unsettled_opt = ''; + trade_or_settle = 'settle_date'; END IF; - sqlquery := 'WITH temp AS (SELECT bond_trades.identifier, asset_class, settle_date, folder, - principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) ) - OVER (PARTITION BY bond_trades.identifier) notional FROM bond_trades WHERE trade_date<=$1 - AND fund=$3) - SELECT DISTINCT ON (identifier) securities.identifier, securities.description, notional, - substring(folder::text FROM ''(?:M_)?(.*)'')::strategy AS folder, - securities.coupon, start_accrued_date, settle_date, temp.principal_payment, - temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type, securities.figi - 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'; + sqlquery := format( + 'WITH temp AS (SELECT bond_trades.identifier, asset_class, settle_date, folder,' + ' principal_payment, accrued_payment, sum(faceamount*(2*buysell::int-1) )' + ' OVER (PARTITION BY bond_trades.identifier) notional' + ' FROM bond_trades' + ' WHERE %I<=$1 AND fund=$3)' + 'SELECT DISTINCT ON (identifier) securities.identifier, securities.description, notional,' + 'substring(folder::text FROM ''(?:M_)?(.*)'')::strategy AS folder,' + 'securities.coupon, start_accrued_date, settle_date, temp.principal_payment,' + 'temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type, securities.figi ' + 'FROM temp LEFT JOIN securities USING (identifier) ' + 'WHERE (temp.notional>0) AND paid_down>$1 %s ' + 'ORDER BY identifier, settle_date desc', + trade_or_settle, + asset_opt); RETURN QUERY EXECUTE sqlquery USING p_date, p_class, p_fund; END; $$ LANGUAGE plpgsql; |