[SQL] Futures : Cascaded deals link back to Parent deal

[SQL] Futures : Cascaded deals link back to Parent deal

 by Israr Ahmed

The following SQL can be used to link together cascaded futures back to their parent contracts. It’s a useful SQL as an audit report and to track the relationship and ensure that the position and price have cascades correctly.

[Sometimes copying and pasting from another source introduces unseen line breaks / characters into the paste. To make sure this is a clean copy / paste into the Endur ISQL window , first use this ‘remove line break’ site to clean up the SQL ]


SELECT rt.parent_tran_num , ab1.toolset as parent_toolset , ab1.ins_type as parent_ins_type , m1.contract_code as parent_contract_code , h1.ticker asparent_ticker , m1.expiration_date as parent_expiration_date , ab1.trade_date as parent_trade_date ,rt.roll_tran_num as child_tran_num , ab2.toolset as child_toolset , ab2.ins_type as child_ins_type , m2.contract_code as child_contract_code,h2.ticker as child_ticker , ab2.trade_date as child_trade_date , ab2.price as child_deal_price


FROM roll_trade_record rt

LEFT OUTER JOIN ab_tran ab1 ON ( rt.parent_tran_num = ab1.tran_num )

LEFT OUTER JOIN ab_tran ab2 ON ( rt.roll_tran_num = ab2.tran_num )

LEFT OUTER JOIN header h1 ON ( ab1.ins_num = h1.ins_num )

LEFT OUTER JOIN header h2 ON ( ab2.ins_num = h2.ins_num )

LEFT OUTER JOIN misc_ins m1 ON ( h1.ins_num = m1.ins_num )

LEFT OUTER JOIN misc_ins m2 ON ( h2.ins_num = m2.ins_num )



( rt.parent_tran_num > 0 )



Have your say:

Subscribe To Read This Article

We regularly publish great content from our experts advising you on how to maximise the efficiency of your trading software and business intelligence suites. 

Download PDF version

This field is for validation purposes and should be left unchanged.