1 files changed, 41 insertions, 5 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index a4e69bfc..c6360a70 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -549,7 +549,9 @@ CREATE OR REPLACE function list_tranche_marks(p_date date)
 RETURNS TABLE(security_id varchar(12), security_desc varchar(32), p_index index_type,
               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) AS $$
+              accrued float, initial_margin_percentage float, theta float,
+              duration float, tranchedelta float, trancheupfrontmid float,
+              indexrefprice float, indexrefspread smallint) AS $$
 DECLARE
   days integer;
   eur_fx float;
@@ -564,7 +566,10 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, d.basketid, d.tenor,
               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
-              ORDER by basketid, tenor, attach, detach, quotedate desc)
+              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
+              ORDER by series, attach, detach, tenor, date 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,
@@ -572,9 +577,11 @@ SELECT temp.security_id, temp.security_desc, temp.index, temp.series, temp.versi
        (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END),
        temp.notional * temp.fact * tranche_spread/10000. * days / 360 *
        (CASE WHEN temp.currency = 'EUR' THEN eur_fx ELSE 1 END),
-       temp.initial_margin_percentage
+       temp.initial_margin_percentage, risk_num.theta, risk_num.duration, risk_num.tranchedelta,
+       risk_num.trancheupfrontmid, risk_num.indexrefprice, risk_num.indexrefspread
 FROM temp
-LEFT JOIN tranche_price USING (basketid, attach, detach, tenor);
+LEFT JOIN tranche_price USING (basketid, attach, detach, tenor)
+LEFT JOIN risk_num USING (series, attach, detach, tenor);
 END
 $$ LANGUAGE plpgsql;
 
@@ -827,7 +834,7 @@ CREATE FOREIGN TABLE priced_percentiles(
      modDur float,
      modDur_io float,
      modDur_po float,
-     wal float,s
+     wal float,
      wal_width float,
      wal_io float,
      wal_po float)
@@ -920,6 +927,35 @@ CREATE FOREIGN TABLE markit_tranche_quotes(
        index_price float)
 SERVER postgresql_server;
 
+CREATE FOREIGN TABLE risk_num_per_quote(
+       tranche_id integer,
+       date date,
+       index text,
+       series integer,
+       tenor tenor,
+       index_price float,
+       index_basis float,
+       "index_EL" float,
+       index_duration float,
+       index_theta float,
+       attach smallint,
+       detach smallint,
+       corr_at_detach float,
+       delta float,
+       forward_delta float,
+       gamma float,
+       theta float,
+       corr01 float,
+       duration float,
+       "EL" float,
+       trancheupfrontmid float,
+       trancherunningmid float,
+       indexrefprice float,
+       indexrefspread smallint,
+       tranchedelta float,
+       quotesource text)
+  SERVER postgresql_server;
+
 CREATE FOREIGN TABLE model_versions(
      model_id smallint,
      asset_class text,