diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 31 |
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( |