diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 21 |
1 files changed, 8 insertions, 13 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 113a0c74..50af3d17 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -850,8 +850,8 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ p_series smallint, p_version smallint, p_tenor tenor, maturity date, notional float, factor float, coupon integer, clean_nav float, accrued float, initial_margin_percentage float, theta float, - duration float, tranchedelta float, trancheupfrontmid float, - indexrefprice float, indexrefspread smallint, + duration float, tranchedelta float4, trancheupfrontmid float, + indexrefprice float4, indexrefspread smallint, attach smallint, detach smallint, index_duration float) AS $$ DECLARE days integer; @@ -865,25 +865,20 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor, FROM list_tranche_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_maturity d USING (index, series, maturity)), -tranche_price AS (SELECT DISTINCT ON (basketid, tenor, orig_attach, orig_detach) basketid, tenor, - e.attach AS orig_attach, e.detach AS orig_detach, upfront_mid, tranche_spread FROM - markit_tranche_quotes e WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date - ORDER by basketid, tenor, e.attach, e.detach, quotedate desc), -risk_num AS (SELECT DISTINCT ON (series, b.attach, b.detach, tenor) * from risk_num_per_quote b - WHERE date BETWEEN p_date - interval '1 week' AND p_date - ORDER by series, b.attach, b.detach, tenor, date desc) +risk_num AS (SELECT DISTINCT ON (index, series, a.attach, a.detach, tenor) * from risk_num_per_quote a + WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date + ORDER by index, series, a.attach, a.detach, tenor, quotedate desc) SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.version, temp.tenor, temp.maturity, temp.notional, temp.fact, - tranche_spread::integer, - temp.notional * temp.fact * upfront_mid * + trancherunningmid::integer, + temp.notional * temp.fact * (case when temp.index = 'HY' then (1.-risk_num.trancheupfrontmid/100) else risk_num.trancheupfrontmid/100 end) * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), - -temp.notional * temp.fact * tranche_spread/10000. * days / 360 * + -temp.notional * temp.fact * trancherunningmid/10000. * days / 360 * (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END), temp.initial_margin_percentage, risk_num.theta, risk_num.duration, risk_num.tranchedelta, risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, temp.orig_attach, temp.orig_detach, risk_num.index_duration FROM temp -LEFT JOIN tranche_price USING (basketid, orig_attach, orig_detach, tenor) LEFT JOIN risk_num ON temp.series=risk_num.series AND temp.orig_attach = risk_num.attach |