diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 32 |
1 files changed, 20 insertions, 12 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index c2568878..4dada8d1 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1031,7 +1031,7 @@ BEGIN RETURN QUERY -- TODO: fix the case of multiple index versions SELECT DISTINCT ON (a.dealid) a.dealid, a.security_id, c.maturity, - (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * a.notional, + (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike, a.expiration_date, b.market_value, endbooknav, a.initial_margin_percentage, b.date, b.delta, b.gamma, b.vega, @@ -1041,27 +1041,33 @@ LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid) LEFT JOIN index_desc c ON a.security_id=redindexcode AND a.maturity=c.maturity LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, tenor) LEFT JOIN valuation_reports ON invid = a.globeop_id -WHERE (termination_date IS NULL OR termination_date > p_date) +LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount + FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid) +WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND trade_date <= p_date AND swap_type = 'CD_INDEX_OPTION' -AND periodenddate = p_date -ORDER BY dealid, date DESC; -END +ORDER BY dealid, date DESC, periodenddate DESC +END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION list_ir_swaption_positions(IN p_date date) +CREATE OR REPLACE FUNCTION list_ir_swaption_positions(IN p_date date, p_fund fund DEFAULT 'SERCGMAST'::fund) RETURNS TABLE(deal_id character varying, strategy swaption_strat, notional double precision, option_type option_type, strike double precision, expiration_date date, maturity date, initial_margin_percentage double precision) AS $$ BEGIN RETURN QUERY -SELECT a.dealid, folder, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * a.notional, +SELECT a.dealid, folder, + (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike, a.expiration_date, a.maturity, a.initial_margin_percentage -FROM swaptions a WHERE (termination_date IS NULL OR termination_date > p_date) +FROM swaptions a +LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount + FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) +WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND a.trade_date <= p_date -AND a.swap_type = 'SWAPTION'; -END +AND a.swap_type = 'SWAPTION' +AND fund=p_fund; +END; $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_abscds_marks(p_date date) @@ -1747,7 +1753,7 @@ CREATE TABLE strategy_im( CREATE TYPE cash_rate AS ENUM('FED_FUND', '1M_LIBOR', '3M_LIBOR'); CREATE OR REPLACE VIEW swaption_trades AS -SELECT id, +SELECT swaptions.id, dealid, termination_date as trade_date, notional, @@ -1766,7 +1772,8 @@ SELECT id, strike, NOT buysell AS buysell, -termination_fee AS fee -FROM swaptions JOIN counterparties ON termination_cp = code WHERE termination_date IS NOT NULL +FROM terminations RIGHT JOIN swaptions USING (dealid) +LEFT JOIN counterparties ON termination_cp = code UNION ALL ( SELECT id, dealid, @@ -1815,6 +1822,7 @@ SELECT cds.id, ref FROM terminations LEFT JOIN cds USING (dealid) left JOIN counterparties on termination_cp=code +WHERE trade_date is NOT NULL UNION ALL ( SELECT id, dealid, |