diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 37 |
1 files changed, 18 insertions, 19 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index a680878d..4a3cf6c2 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -606,29 +606,28 @@ END $$ LANGUAGE plpgsql; CREATE OR REPLACE function list_swaption_positions_and_risks (p_date date) -RETURNS TABLE(deal_id varchar(28), security_id varchar(12), maturity date, notional float, swaption_type swaption_type, strike float, - expiration_date date, market_value float, initial_margin_percentage float, latest_model_date date, delta float, gamma float, vega float, index index_type, series smallint, tenor tenor, duration float) AS $$ +RETURNS TABLE(deal_id varchar(28), security_id varchar(12), notional float, + swaption_type swaption_type, strike float, expiration_date date, + market_value float, initial_margin_percentage float, + latest_model_date date, delta float, gamma float, vega float, theta float, + index index_type, series smallint, tenor tenor, duration float) AS $$ BEGIN RETURN QUERY -with tmp as (select * from index_quotes g inner JOIN index_desc USING (index, series, version, tenor) where g.date=p_date) -SELECT f.*, e.index, e.series, e.tenor, e.duration from tmp e -inner join ( - select c.dealid, c.security_id, c.maturity, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * c.notional as notional, c.swaption_type, c.strike, c.expiration_date, d.market_value, c.initial_margin_percentage, d.date as latest_model_date, d.delta, d.gamma, d.vega - from swaptions c - left join( - select a.dealid, a.date, a.market_value, a.delta, a.gamma, a.vega from swaption_marks as a - inner join ( - select dealid, max(date) as latest_model_date - from swaption_marks - where date <= p_date - group by dealid) as b - on a.dealid = b.dealid and date = b.latest_model_date - ) d on c.dealid = d.dealid - where c.trade_date <= p_date and c.expiration_date > p_date ORDER BY dealid ASC) f -on (f.security_id = e.redindexcode AND e.maturity =f.maturity); -END; +SELECT DISTINCT ON (a.dealid) a.dealid, a.security_id, + (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * a.notional, + a.swaption_type, a.strike, a.expiration_date, b.market_value, + a.initial_margin_percentage, b.date, b.delta, b.gamma, b.vega, + b.theta, c.index, c.series, c.tenor, d.duration +FROM swaptions a +JOIN swaption_marks b USING (dealid) +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, version) +WHERE termination_date IS NULL AND a.expiration_date > p_date AND trade_date <= p_date +ORDER BY dealid, date DESC; +END $$ LANGUAGE plpgsql; + CREATE OR REPLACE function list_abscds_marks(p_date date) RETURNS TABLE(security_id varchar(12), cusip varchar(9), security_desc varchar(32), maturity date, notional float, factor float, fixed_rate float, clean_nav float, accrued float) AS $$ |