diff options
| -rw-r--r-- | sql/dawn.sql | 48 |
1 files changed, 48 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 529b2d73..a8f59409 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3861,6 +3861,54 @@ 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_crse +AS SELECT tranche_risk.date, + tranche_risk.tranche_id AS trade_id, + a.admin_id, + cds.security_desc, + index_version.index, + index_version.series, + cds.maturity, + cds.orig_attach, + cds.orig_detach, + tranche_risk.notional, + a.admin_notional, + tranche_risk.clean_nav AS serenitas_clean_nav, + a.admin_clean_nav, + tranche_risk.accrued AS serenitas_accrued, + a.admin_accrued, + external_marks_deriv.base_nav AS cpty_nav, + tranche_risk.duration, + tranche_risk.delta, + tranche_risk.gamma, + tranche_risk.theta, + tranche_risk.theta_amount, + tranche_risk.tranche_factor, + tranche_risk.corr_attach, + tranche_risk.corr_detach, + tranche_risk.upfront, + tranche_risk.running, + tranche_risk.index_refprice, + tranche_risk.index_refspread, + tranche_risk.index_duration, + tranche_risk.hy_equiv, + cds.initial_margin_percentage, + tranche_risk.ir_dv01 + FROM tranche_risk + LEFT JOIN cds ON tranche_risk.tranche_id = cds.id + FULL JOIN ( SELECT COALESCE(isosel_accrued.init_trader_notes, isosel_accrued.init_fix_id) AS fix_id, + isosel_accrued.init_order_id AS admin_id, + isosel_accrued.period_end_date, + sum(isosel_accrued.total_acc_int) AS admin_accrued, + sum(isosel_accrued.end_mkt_value) AS admin_clean_nav, + sum(- isosel_accrued."position") AS admin_notional + FROM isosel_accrued + WHERE isosel_accrued.liqd_date IS NULL + GROUP BY (COALESCE(isosel_accrued.init_trader_notes, isosel_accrued.init_fix_id)), isosel_accrued.init_order_id, isosel_accrued.period_end_date) a ON a.fix_id = concat('SCCDS', tranche_risk.tranche_id) AND a.period_end_date = tranche_risk.date + LEFT JOIN index_version ON cds.security_id::text = index_version.redindexcode + LEFT JOIN external_marks_deriv ON cds.cpty_id = external_marks_deriv.identifier AND external_marks_deriv.date = tranche_risk.date + WHERE cds.fund = 'CRSE'::fund + ORDER BY index_version.index, index_version.series, cds.orig_attach; CREATE TABLE isosel_accrued ( "status" text NULL, |