select 
o.id,o.bond_id+0 bond_id,o.created_stamp,UNIX_TIMESTAMP(o.created_stamp) created_at,o.bs direction,(o.outright_size-o.outright_filled) size,o.outright_filled,
o.outright_price price,o.user_id client_id,g.company_id,do.bmp_lock,o.price_type,ao.id engaged_by,o.x_order_id,o.expiry_stamp,0 expiry_seconds,co.counter_id,
o.bd_type,o.status,if(o.bd_type='Counter' and o.reason='Chain cancel' and co.ref_id is null,if(ro.reason in ('Expiry','BenchmarkChange'),ro.reason,'rejected'),
o.reason) reason,b.trading_type,o.commission,b.type bond_type,o.exec_stamp2,co.ref_id,if(do.bmp_lock=9,1,0) placement_pending, o.is_nit_firm is_live, 
coalesce(bt.id,st.id) trade_id 
from orders o 
join bond b on b.id=o.bond_id 
join user u on u.id=o.user_id 
join groups g on g.id=u.group_id 
left join dark_orders do on do.id=o.id 
left join counter_orders co on co.id=o.id 
left join orders ro on co.counter_id=ro.id 
left join counter_orders co2 on co2.counter_id=o.id and o.status='ACTIVE' 
left join orders ao on co2.id=ao.id and ao.status='ACTIVE' 
left join trades bt on bt.b_order=o.id 
left join trades st on st.s_order=o.id 
where b.status=1 and b.type in ('NewIssue','PricedNewIssue') and (b.market='EM' or b.market in ('IG','TSY') or b.is_hy=1) 
  and b.currency in ('USD','EUR','GBP','CHF','CAD','MXN','ZAR','HUF') and (b.series <> 'regs' or b.seasoning=0) and (o.id=20527502) 
UNION ALL 
select o.id,o.bond_id+0 bond_id,o.created_stamp,UNIX_TIMESTAMP(o.created_stamp) created_at,o.bs direction,(o.outright_size-o.outright_filled) size,
o.outright_filled,o.outright_price price,o.user_id client_id,g.company_id,null bmp_lock,o.price_type,null engaged_by,o.x_order_id,o.expiry_stamp,0 expiry_seconds,
null counter_id,o.bd_type,o.status,if(o.bd_type='Counter' and o.reason='Chain cancel','rejected',o.reason) reason,b.trading_type,o.commission,b.type bond_type,
o.exec_stamp2,null ref_id,0 placement_pending, o.is_nit_firm is_live, coalesce(bt.id,st.id) trade_id 
from arc_orders o 
join bond b on b.id=o.bond_id 
join user u on u.id=o.user_id 
join groups g on g.id=u.group_id 
left join counter_orders co on co.id=o.id 
left join arc_trades bt on bt.b_order=o.id 
left join arc_trades st on st.s_order=o.id 
where b.status=1 and b.type in ('NewIssue','PricedNewIssue') and (b.market='EM' or b.market in ('IG','TSY') or b.is_hy=1) 
  and b.currency in ('USD','EUR','GBP','CHF','CAD','MXN','ZAR','HUF') and (b.series <> 'regs' or b.seasoning=0) and (o.id=20527502)
  and o.created_stamp>'2023-08-14'-interval 7 day