1 files changed, 53 insertions, 9 deletions
diff --git a/python/report_ops/queries.py b/python/report_ops/queries.py
index e1ec9c9c..2ae517aa 100644
--- a/python/report_ops/queries.py
+++ b/python/report_ops/queries.py
@@ -1,17 +1,61 @@
 BOND_QUERY = "SELECT * FROM risk_positions(%s, null, %s) "
 
-FUTURE_QUERY = (
-    "WITH tmp AS (SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell, sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional FROM futures "
-    "WHERE trade_date <= %s AND fund=%s) "
-    "SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell FROM tmp LEFT JOIN accounts USING (fund) WHERE tmp.notional != 0 AND account_type='Future';"
+FUTURE_QUERY = """
+WITH tmp AS (
+  SELECT bbg_ticker, fund, security_desc, currency, maturity, account_code, dealid, buysell,
+         sum(quantity * (2*buysell::int-1)) OVER (PARTITION BY bbg_ticker, fund, security_desc, currency, maturity) notional
+  FROM futures
+  WHERE trade_date <= %s AND fund=%s
 )
+SELECT bbg_ticker, notional, code AS cp_code, cash_account, security_desc, currency, maturity, account_code, dealid, buysell
+FROM tmp
+LEFT JOIN accounts USING (fund)
+WHERE tmp.notional != 0 AND account_type='Future';
+"""
 
-TRANCHE_QUERY = "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional * trb.tranche_factor as active_notional, cds.* FROM tranche_risk_master trb left join cds on trade_id=id WHERE date=%s AND trb.fund=%s;"
+TRANCHE_QUERY = """
+SELECT trm.trade_id,
+       trm.serenitas_clean_nav + trm.serenitas_accrued AS mtm,
+       trm.notional * trm.tranche_factor AS active_notional,
+       cds.*,
+       b.redindexcode
+FROM tranche_risk_master trm
+LEFT JOIN cds ON trm.trade_id = cds.id
+LEFT JOIN LATERAL (
+   SELECT INDEX, series, redindexcode
+   FROM index_version iv
+   WHERE iv.series = trm.series
+     AND iv.index = trm.index
+     AND lastdate >= trm.date
+) b ON TRUE
+WHERE trm.date = %s
+  AND cds.fund = %s
+"""
 
-CDX_SWAPTION_QUERY = "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, %s) spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;"
+CDX_SWAPTION_QUERY = """
+SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate
+FROM list_swaption_positions_and_risks(%s, %s) spr
+LEFT JOIN swaptions ON deal_id=dealid
+LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;
+"""
 
-IR_SWAPTION_QUERY = "SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, %s) spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;"
+IR_SWAPTION_QUERY = """
+SELECT abs(spr.notional) AS active_notional, spr.nav as serenitas_nav, swaptions.*, index_version_markit.effectivedate
+FROM list_ir_swaption_positions(%s, %s) spr
+LEFT JOIN swaptions ON deal_id=dealid
+LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;
+"""
 
-CDX_QUERY = "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, %s) cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;"
+CDX_QUERY = """
+SELECT cds.*, ivm.effectivedate
+FROM list_cds_marks(%s, null, %s) cds
+LEFT JOIN index_version_markit ivm ON security_id=redindexcode;
+"""
 
-IRS_QUERY = "SELECT isr.pv, irs.*, accounts2.name FROM ir_swap_risk isr LEFT JOIN irs ON id=swp_id LEFT JOIN accounts2 USING (cash_account) WHERE date=%s AND irs.fund=%s;"
+IRS_QUERY = """
+SELECT isr.pv, irs.*, accounts2.name
+FROM ir_swap_risk isr
+LEFT JOIN irs ON id=swp_id
+LEFT JOIN accounts2 USING (cash_account)
+WHERE date=%s AND irs.fund=%s;
+"""