diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e57ea3cc..fe0feeb0 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -241,6 +241,33 @@ BEGIN END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE function list_positions_range(start_date date, + end_date date, + p_class asset_class DEFAULT NULL) +RETURNS TABLE(identifier varchar(12), description varchar(32), notional float, strategy bond_strat, + curr_cpn float, start_accrued_date date, last_settle_date date, + principal_payment float, accrued_payment float, currency currency, daycount day_count, + bbg_type bbg_type) AS $$ +DECLARE sqlquery text; +DECLARE asset_opt text; +DECLARE unsettled_opt text; +BEGIN + + 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 <=$2) + SELECT DISTINCT ON (temp.identifier) temp.identifier, securities.description, notional, folder, + securities.coupon, start_accrued_date, settle_date, temp.principal_payment, + temp.accrued_payment, securities.currency, securities.day_count, securities.bbg_type + FROM temp LEFT JOIN securities USING (identifier) + WHERE (temp.notional>0 or (temp.notional==0 and settle_date>=$1)) AND paid_down>$1 '||asset_opt + ||' ORDER BY identifier, settle_date desc'; + RETURN QUERY EXECUTE sqlquery USING start_date, end_date, p_class; +END; +$$ LANGUAGE plpgsql; + + CREATE OR REPLACE function risk_positions(p_date date, p_assetclass asset_class) RETURNS TABLE (description varchar(32), identifier varchar(12), notional float, price float, strategy bond_strat, factor float, local_market_value float, usd_market_value float, |