1 files changed, 16 insertions, 15 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql
index f60c8ed9..6482803b 100644
--- a/sql/dawn.sql
+++ b/sql/dawn.sql
@@ -943,7 +943,7 @@ WITH temp AS (SELECT a.*, c.index, c.series, c.version, c.basketid, d.tenor,
               LEFT JOIN index_version c ON a.security_id=c.redindexcode
               LEFT JOIN index_maturity d USING (index, series, maturity)),
 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
+              WHERE quotedate BETWEEN p_date - interval '1 week' AND p_date + interval '1 day'
               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,
@@ -1636,20 +1636,21 @@ for t in (df.itertuples(index=False)):
 $$ LANGUAGE plpythonu;
 
 
-CREATE TABLE tranche_marks(
-       date date NOT NULL,
-       id integer NOT NULL REFERENCES cds,
-       clean_nav float,
-       accrued float,
-       tranche_duration float,
-       spread float,
-       delta float,
-       gamma float,
-       theta float,
-       corr_attach float,
-       corr_detach float,
-       index_duration float,
-       PRIMARY KEY(date, id))
+CREATE OR REPLACE VIEW globeop_tranche_risk AS
+SELECT date, security_desc, index, series, maturity, orig_attach,
+       orig_detach, CASE protection WHEN 'Buyer' THEN notional ELSE -notional END AS notional,
+       endqty as globeop_notional,
+       clean_nav as serenitas_clean_nav,
+       endbooknav-endbookunrealincome as globeop_clean_nav,
+       accrued as serenitas_accrued, endbookunrealincome,
+       duration, delta, gamma, theta, tranche_risk.corr_attach, tranche_risk.corr_detach,
+       tranche_risk.upfront, tranche_risk.running,
+       index_refprice, index_refspread, index_duration
+FROM tranche_risk
+LEFT JOIN cds ON (tranche_id=id)
+LEFT JOIN valuation_reports ON (invid=globeop_id AND periodenddate=date)
+LEFT JOIN index_version ON (security_id=redindexcode)
+ORDER BY index, series, orig_attach;
 
 
 CREATE TABLE fcm_im(