diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 29 |
1 files changed, 29 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index ba7164cc..4c224eb6 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3844,6 +3844,35 @@ AS SELECT tranche_risk.date, WHERE cds.fund = 'ISOSEL'::fund ORDER BY index_version.index, index_version.series, cds.orig_attach; +CREATE OR REPLACE VIEW tranche_risk_globeop AS +SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, index, series, maturity, orig_attach, + orig_detach, tranche_risk.notional, + admin_notional, + clean_nav as serenitas_clean_nav, + admin_clean_nav, + accrued as serenitas_accrued, + admin_accrued, + base_nav AS cpty_nav, + duration, delta, gamma, theta, theta_amount, tranche_factor, + tranche_risk.corr_attach, tranche_risk.corr_detach, + tranche_risk.upfront, tranche_risk.running, + index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage, cds.fund +FROM tranche_risk +LEFT JOIN LATERAL +(SELECT globeop_id FROM id_mapping WHERE id_mapping.serenitas_id=tranche_id AND date <= tranche_risk.date ORDER by date desc LIMIT 1) b ON true +LEFT JOIN cds ON (tranche_id=id) +FULL JOIN (SELECT invid, periodenddate, + sum(endbookunrealincome) AS admin_accrued, + sum(endbooknav-endbookunrealincome) AS admin_clean_nav, + sum(endqty) AS admin_notional + FROM valuation_reports GROUP BY invid, periodenddate) a + ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00HEEAD'), '')::integer=b.globeop_id AND periodenddate=tranche_risk.date) +LEFT JOIN index_version ON (security_id=redindexcode) +LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date +WHERE fund IN ('SERCGMAST', 'BOWDST') +ORDER BY index, series, orig_attach; + + CREATE OR REPLACE VIEW tranche_risk_citco AS SELECT tranche_risk.date, tranche_risk.tranche_id AS trade_id, |