diff options
| -rw-r--r-- | sql/dawn.sql | 32 |
1 files changed, 32 insertions, 0 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 19a5c224..cf5a8890 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -752,6 +752,12 @@ CREATE TABLE terminations ( partial_termination bool NOT null default false, new_gtid int, fee_payment_date date NOT NULL, + traded_level numeric(9, 5) NULL, + "deal_type" public."deal_type" NOT NULL, + "currency" public."currency" NOT NULL, + delta_id int4 NULL, + delta_alloc float8 NULL, + cpty_id text null ); @@ -4893,3 +4899,29 @@ WHERE trade_ccy IN ('USD', 'EUR') GROUP BY period_end_date, fund, "prime_broker/clearing_broker", COALESCE (split_part("strategy", '/', 1), 'CASH'), COALESCE (split_part("strategy", '/', 2), 'CSH_CASH') ORDER BY period_end_date desc; + +CREATE VIEW cds_termination_mapping AS +SELECT t.id AS termination_id, cds.* +FROM terminations t +LEFT JOIN cds ON t.deal_type = 'CDS' AND t.dealid = cds.dealid WHERE deal_type='CDS'; + +CREATE VIEW swaption_termination_mapping AS +SELECT t.id AS termination_id, swaptions.* +FROM terminations t +LEFT JOIN swaptions ON (t.deal_type = 'CreditSwaption' OR t.deal_type = 'IRSwaption') AND t.dealid = swaptions.dealid WHERE deal_type IN ('CreditSwaption', 'IRSwaption'); + + +CREATE VIEW trs_termination_mapping AS +SELECT t.id AS termination_id, trs.* +FROM terminations t +LEFT JOIN trs ON t.deal_type = 'TRS' AND t.dealid = trs.dealid WHERE deal_type='TRS'; + +CREATE VIEW termination_collateral_mapping AS +SELECT * FROM (SELECT fund, folder::text, termination_id FROM cds_termination_mapping +UNION +SELECT fund, folder::text, termination_id FROM swaption_termination_mapping +UNION +SELECT fund, folder::text, termination_id from trs_termination_mapping) a LEFT JOIN +terminations on a.termination_id = terminations.id; + + |