diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 20 |
1 files changed, 20 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index e19c3207..4a863ed6 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -496,6 +496,26 @@ LEFT JOIN tranche_price USING (basketid, attach, detach, tenor); 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, latest_model_date date, delta float, gamma float, vega 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.date as latest_model_date, d.delta, d.gamma, d.vega +from swaptions c +inner 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; +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 $$ |