diff options
| -rw-r--r-- | sql/dawn.sql | 14 |
1 files changed, 12 insertions, 2 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index f8471817..d71d5783 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -3021,6 +3021,15 @@ create table id_mapping( PRIMARY KEY (date, trade_type, serenitas_id) ); +CREATE TABLE citco_id_mapping ( + "date" date NOT NULL, + "trade_type" text NOT NULL, + serenitas_id int4 NOT NULL, + citco_id text NOT NULL, + PRIMARY KEY (date, trade_type, serenitas_id) +); + + CREATE TABLE tranche_cashflows( date date NOT NULL, tranche_id integer NOT NULL, @@ -3829,7 +3838,8 @@ AS SELECT tranche_risk.date, cds.initial_margin_percentage, tranche_risk.ir_dv01 FROM tranche_risk - LEFT JOIN cds ON tranche_risk.tranche_id = cds.id + LEFT JOIN cds ON tranche_risk.tranche_id = cds.id + LEFT JOIN lateral (SELECT citco_id FROM citco_id_mapping WHERE date >= tranche_risk.date AND serenitas_id=tranche_risk.tranche_id ORDER BY date limit 1) cim 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, @@ -3838,7 +3848,7 @@ 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 = coalesce(cim.citco_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 = 'ISOSEL'::fund |