diff options
| -rw-r--r-- | sql/dawn.sql | 30 |
1 files changed, 17 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index fac6ab92..1b130a52 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -604,21 +604,25 @@ $$ 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) AS $$ + 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 $$ BEGIN RETURN QUERY -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; +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; $$ LANGUAGE plpgsql; |