diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 38 |
1 files changed, 37 insertions, 1 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 9f9d3440..ca77595d 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -2454,6 +2454,42 @@ LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.da WHERE fund='SERCGMAST' ORDER BY index, series, orig_attach; +CREATE OR REPLACE VIEW ir_swaption_risk_serenitas AS +SELECT ir_swaption_risk.date, swpt_id as trade_id, swaptions.globeop_id, security_desc, maturity, + admin_notional, + pv as serenitas_pv, + admin_pv, + base_nav AS cpty_nav, + vol, vol_type, "DV01", "IRGamma1bp", vega + FROM ir_swaption_risk +LEFT JOIN swaptions ON (swpt_id=id) +FULL JOIN (SELECT invid, periodenddate, + sum(endbooknav) AS admin_pv, + sum(endqty) AS admin_notional + FROM valuation_reports GROUP BY invid, periodenddate) a + ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00SCLMA'), '')::integer=swaptions.globeop_id AND periodenddate=ir_swaption_risk.date) +LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=ir_swaption_risk.date +WHERE fund='SERCGMAST' +ORDER BY ir_swaption_risk.date DESC; + +CREATE OR REPLACE VIEW ir_swaption_risk_bowdst AS +SELECT ir_swaption_risk.date, swpt_id as trade_id, swaptions.globeop_id, security_desc, maturity, + admin_notional, + pv as serenitas_pv, + admin_pv, + base_nav AS cpty_nav, + vol, vol_type, "DV01", "IRGamma1bp", vega + FROM ir_swaption_risk +LEFT JOIN swaptions ON (swpt_id=id) +FULL JOIN (SELECT invid, periodenddate, + sum(endbooknav) AS admin_pv, + sum(endqty) AS admin_notional + FROM valuation_reports GROUP BY invid, periodenddate) a + ON (NULLIF(substring(invid FROM '\D{3}[0]*(\d*)K00HEEAD'), '')::integer=swaptions.globeop_id AND periodenddate=ir_swaption_risk.date) +LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=ir_swaption_risk.date +WHERE fund='BOWDST' +ORDER BY ir_swaption_risk.date DESC; + -- 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, @@ -3421,4 +3457,4 @@ CREATE table isda_agreement_dates( counterparty text REFERENCES counterparties(code), agreement_date date not null, CONSTRAINT isda_agreement_dates_key UNIQUE (fund, counterparty, agreement_date), -)
\ No newline at end of file +) |