diff options
| -rw-r--r-- | sql/dawn.sql | 22 |
1 files changed, 11 insertions, 11 deletions
diff --git a/sql/dawn.sql b/sql/dawn.sql index 79371b40..89e07fad 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1200,11 +1200,11 @@ RETURN QUERY SELECT a.dealid, folder, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.amount - coalesce(terminated_amount, 0.)), a.cap_or_floor, a.strike, a.floating_rate_index, a.expiration_date, a.initial_margin_percentage, - a.comments, a.cp_code, c.nav + a.comments, a.cp_code, c.base_nav FROM capfloors a LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) -LEFT join (select * from external_marks_deriv where date = p_date) c on a.cpty_id = c.identifier +LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv WHERE date=p_date) c ON a.cpty_id = c.identifier WHERE a.amount IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND a.trade_date <= p_date @@ -1221,11 +1221,11 @@ RETURN QUERY SELECT a.dealid, folder, (CASE WHEN buysell = 't' THEN 1 ELSE -1 END) * (a.notional - coalesce(terminated_amount, 0.)), a.option_type, a.strike, a.security_id, a.expiration_date, a.maturity, a.initial_margin_percentage, - a.cp_code, c.nav + a.cp_code, c.base_nav FROM swaptions a LEFT JOIN (SELECT dealid, SUM(termination_amount) AS terminated_amount FROM terminations WHERE termination_date <= p_date GROUP BY dealid) b USING (dealid) -LEFT join (select * from external_marks_deriv where date = p_date) c on a.cpty_id = c.identifier +LEFT JOIN (SELECT identifier, base_nav FROM external_marks_deriv where date = p_date) c on a.cpty_id = c.identifier WHERE a.notional IS DISTINCT FROM terminated_amount AND a.expiration_date > p_date AND a.trade_date <= p_date @@ -1878,7 +1878,7 @@ SELECT tranche_risk.date, tranche_id as trade_id, b.globeop_id, security_desc, i clean_nav * coalesce(fx, 1.) as serenitas_clean_nav, admin_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, admin_accrued, - nav AS cpty_nav, + base_nav AS cpty_nav, duration, delta, gamma, theta, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, @@ -1895,7 +1895,7 @@ RIGHT JOIN (SELECT invid, periodenddate, ON (invid=b.full_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 +LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date ORDER BY index, series, orig_attach; CREATE OR REPLACE VIEW tranche_risk_bowdst AS @@ -1906,17 +1906,17 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, admin_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, NULL AS admin_accrued, - nav AS cpty_nav, + base_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 as_of_date, link_ref, current_notional * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_notional, base_market_value * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_clean_nav FROM bowdst_val WHERE link_ref LIKE 'SCCDS%' aAND base_market_value!=0) b ON link_ref=format('SCCDS%s',tranche_id) AND as_of_date=tranche_risk.date +LEFT JOIN (SELECT as_of_date, link_ref, current_notional * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_notional, base_market_value * (CASE WHEN coupon_rate=0 THEN 1. ELSE -1. END) AS admin_clean_nav FROM bowdst_val WHERE link_ref LIKE 'SCCDS%' AND base_market_value!=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 (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 +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; @@ -1928,7 +1928,7 @@ SELECT tranche_risk.date, tranche_id as trade_id, security_desc, index, series, admin_clean_nav, accrued * coalesce(fx, 1.) as serenitas_accrued, NULL AS admin_accrued, - nav AS cpty_nav, + base_nav AS cpty_nav, duration, delta, gamma, theta, tranche_factor, tranche_risk.corr_attach, tranche_risk.corr_detach, tranche_risk.upfront, tranche_risk.running, @@ -1939,7 +1939,7 @@ LEFT JOIN (SELECT accounting_date, security_id, quantity * (CASE WHEN long_short FROM bbh_val WHERE sub_security_type_code ='CXT' AND interest_rate IS NOT NULL) b ON b.security_id=format('SCCDS%s', tranche_id) AND accounting_date=tranche_risk.date LEFT JOIN index_version ON (cds.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 +LEFT JOIN external_marks_deriv ON cpty_id=identifier AND external_marks_deriv.date=tranche_risk.date WHERE fund='BRINKER' ORDER BY index, series, orig_attach; |