diff options
| -rw-r--r-- | python/collateral/__main__.py | 2 | ||||
| -rw-r--r-- | sql/dawn.sql | 15 |
2 files changed, 16 insertions, 1 deletions
diff --git a/python/collateral/__main__.py b/python/collateral/__main__.py index 327ba97d..86c1916f 100644 --- a/python/collateral/__main__.py +++ b/python/collateral/__main__.py @@ -132,7 +132,7 @@ for fund in funds: ) positions_rates = pd.read_sql_query( "SELECT maturity_date, (fixed_rate /100)::NUMERIC(4, 4) as fixed_rate, float_index, currency, roll_day, notional, folder " - "FROM ir_swap_risk_master WHERE date=%s AND fund=%s", + "FROM list_ir_swap_positions(%s, %s)", dawn_engine, params=( workdate, diff --git a/sql/dawn.sql b/sql/dawn.sql index cc4fda41..0c55e863 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1922,6 +1922,21 @@ AND fund=p_fund; END; $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION list_ir_swap_positions(p_date date, p_fund fund) + RETURNS TABLE(date date, effective_date date, maturity_date date, fixed_rate double precision, fund fund, folder strategy, cash_account text, float_index cash_rate, currency currency, clearing_facility clearing_cp, roll_day roll_day, notional double precision, ids integer[]) + LANGUAGE plpgsql +AS $function$ +BEGIN + RETURN QUERY SELECT p_date, i.effective_date, i.maturity_date, i.fixed_rate, i.fund, i.folder, i.cash_account, i.float_index, i.currency, i.clearing_facility, i.roll_day, + sum(CASE WHEN payreceive THEN i.notional ELSE -i.notional end) AS notional, array_agg(i.id) + FROM irs i + WHERE i.fund = $2 AND i.trade_date <= $1 + GROUP BY i.effective_date, i.maturity_date, i.fixed_rate, i.fund, i.folder, i.cash_account, i.float_index, i.currency, i.clearing_facility, i.roll_day + HAVING sum(CASE WHEN payreceive THEN i.notional ELSE -i.notional end) != 0; +END; +$function$ +; + CREATE VIEW ir_swap_risk_master AS SELECT isr.date, irs.effective_date, |