diff options
| -rw-r--r-- | sql/dawn.sql | 44 |
1 files changed, 36 insertions, 8 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 9b2558ec..366ae126 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -290,7 +290,6 @@ CREATE TABLE cds(id serial primary key, account_code varchar(5) NOT NULL REFERENCES accounts(code), cpty_id text, globeop_id int, - full_globeop_id text GENERATED ALWAYS AS ('CDS'||lpad(cast(globeop_id as text), 6, '0')|| 'K00SCLMA') STORED, stale bool default true, traded_level numeric(9, 5) NULL, exercised_from text REFERENCES swaptions(dealid), @@ -1488,7 +1487,7 @@ FROM swaptions a LEFT JOIN (SELECT * FROM swaption_marks where date <= p_date) b USING (dealid) LEFT JOIN index_desc c ON a.security_id=redindexcode AND a.maturity=c.maturity LEFT JOIN (SELECT * FROM index_quotes WHERE date=p_date) d USING (index, series, tenor) -LEFT JOIN valuation_reports ON invid = a.full_globeop_id +LEFT JOIN valuation_reports ON NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K'), '')::integer = a.globeop_id LEFT JOIN bowdst_val ON link_ref = a.dealid AND as_of_date=p_date LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) e USING (dealid) @@ -2194,6 +2193,7 @@ for t in (df.itertuples(index=False)): $$ LANGUAGE plpython3u; + CREATE OR REPLACE VIEW tranche_risk_serenitas 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, @@ -2208,34 +2208,63 @@ SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, i index_refprice, index_refspread, index_duration, hy_equiv, initial_margin_percentage FROM tranche_risk LEFT JOIN LATERAL -(SELECT full_globeop_id, 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 +(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) RIGHT 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 (invid=b.full_globeop_id AND periodenddate=tranche_risk.date) + ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00SCLMA'), '')::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 ORDER BY index, series, orig_attach; +-- bony reports version +-- CREATE OR REPLACE VIEW tranche_risk_bowdst AS +-- SELECT tranche_risk.date, tranche_id as trade_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, +-- NULL AS 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 +-- FROM tranche_risk +-- LEFT JOIN cds ON (tranche_id=id) +-- LEFT JOIN (SELECT as_of_date, link_ref, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, base_market_value AS admin_clean_nav FROM bowdst_val WHERE security_description_1 LIKE 'TR%NCH%' AND abs(base_market_value) > 1.0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=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='BOWDST' +-- ORDER BY index, series, orig_attach; + CREATE OR REPLACE VIEW tranche_risk_bowdst AS -SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, +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, - NULL AS admin_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 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) -LEFT JOIN (SELECT as_of_date, link_ref, (CASE WHEN coupon_rate=0 THEN current_notional ELSE -current_notional END) AS admin_notional, base_market_value AS admin_clean_nav FROM bowdst_val WHERE security_description_1 LIKE 'TR%NCH%' AND abs(base_market_value) > 1.0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date +RIGHT 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='BOWDST' @@ -2472,7 +2501,6 @@ create table id_mapping( trade_type text not null, serenitas_id int not null, globeop_id int not null, - full_globeop_id text GENERATED ALWAYS AS (trade_type||lpad(globeop_id::text, 6, '0')|| 'K00SCLMA') stored, PRIMARY KEY (date, trade_type, serenitas_id) ); |