diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/dawn.sql | 11 |
1 files changed, 8 insertions, 3 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 3ef3d009..a76c3ebf 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3890,7 +3890,7 @@ WHERE fund IN ('SERCGMAST', 'BOWDST') ORDER BY index, series, orig_attach; -CREATE OR REPLACE VIEW tranche_risk_citco +CREATE OR REPLACE VIEW public.tranche_risk_citco AS SELECT tranche_risk.date, tranche_risk.tranche_id AS trade_id, a.admin_id, @@ -3923,9 +3923,15 @@ AS SELECT tranche_risk.date, tranche_risk.hy_equiv, cds.initial_margin_percentage, tranche_risk.ir_dv01, + a.fix_id, cds.fund FROM tranche_risk LEFT JOIN cds ON tranche_risk.tranche_id = cds.id + LEFT JOIN LATERAL ( SELECT id_mapping.globeop_id + FROM id_mapping + WHERE id_mapping.serenitas_id = tranche_risk.tranche_id AND id_mapping.date <= tranche_risk.date + ORDER BY id_mapping.date DESC + LIMIT 1) b ON true FULL JOIN ( SELECT isosel_accrued.init_fix_id AS fix_id, isosel_accrued.init_order_id AS admin_id, isosel_accrued.period_end_date, @@ -3934,10 +3940,9 @@ AS SELECT tranche_risk.date, sum(- isosel_accrued."position") AS admin_notional FROM isosel_accrued WHERE isosel_accrued.liqd_date IS NULL - GROUP BY 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 + GROUP BY 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) OR a.admin_id = b.globeop_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 = ANY (ARRAY['ISOSEL'::fund, 'CRSE'::fund]) ORDER BY index_version.index, index_version.series, cds.orig_attach; CREATE TABLE isosel_accrued ( |