diff options
| -rw-r--r-- | sql/dawn.sql | 10 |
1 files changed, 6 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 7b4a6d87..00625508 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -568,7 +568,8 @@ RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_ 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) AS $$ + indexrefprice float, indexrefspread smallint, + attach smallint, detach smallint) AS $$ DECLARE days integer; eur_fx float; @@ -581,8 +582,8 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor, FROM list_tranche_positions(p_date) a LEFT JOIN index_version c ON a.security_id=c.redindexcode LEFT JOIN index_desc d USING (redindexcode, maturity)), -tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, attach, detach, upfront_mid, tranche_spread FROM - markit_tranche_quotes WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date +tranche_price AS (SELECT DISTINCT ON (basketid, tenor, attach, detach) basketid, tenor, e.attach, e.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, attach, detach, quotedate desc), risk_num AS (SELECT DISTINCT ON (series, attach, detach, tenor) * from risk_num_per_quote WHERE date BETWEEN p_date - interval '1 week' AND p_date @@ -595,7 +596,8 @@ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.versi temp.notional * temp.fact * tranche_spread/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 + risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread, + risk_num.attach, risk_num.detach FROM temp LEFT JOIN tranche_price USING (basketid, attach, detach, tenor) LEFT JOIN risk_num USING (series, attach, detach, tenor); |