diff options
Diffstat (limited to 'sql/dawn.sql')
| -rw-r--r-- | sql/dawn.sql | 10 |
1 files changed, 6 insertions, 4 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index f8a6f32c..58cbb1f9 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1707,26 +1707,28 @@ $$ LANGUAGE plpythonu; CREATE OR REPLACE VIEW globeop_tranche_risk AS -SELECT date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, +SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, maturity, orig_attach, orig_detach, CASE protection WHEN 'Buyer' THEN notional ELSE -notional END AS notional, endqty as globeop_notional, clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, globeop_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, globeop_accrued, + nav AS cpty_nav, duration, delta, gamma, theta, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, index_refprice, index_refspread, index_duration, initial_margin_percentage FROM tranche_risk LEFT JOIN cds ON (tranche_id=id) -LEFT JOIN (SELECT invid, periodenddate, +RIGHT JOIN (SELECT invid, periodenddate, sum(endbookunrealincome) AS globeop_accrued, - sum(endbooknav-endbookunrealincome) AS globeop_clean_nav + sum(endbooknav-endbookunrealincome) AS globeop_clean_nav, sum(endqty) AS globeop_notional FROM valuation_reports GROUP BY invid, periodenddate) a - ON (invid=globeop_id AND periodenddate=date) + ON (invid=globeop_id AND periodenddate=tranche_risk.date) LEFT JOIN index_version ON (security_id=redindexcode) LEFT JOIN (SELECT date, 'EUR'::currency AS currency, eurusd AS fx FROM fx) fx USING (date, currency) +LEFT JOIN external_marks_deriv on cpty_id=identifier and external_marks_deriv.date=tranche_risk.date ORDER BY index, series, orig_attach; |