diff options
| -rw-r--r-- | python/report_ops/queries.py | 62 |
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; +""" |