Hello, I have a report request show investment orders where at the trade level different commission rates are used. Background: We submit a buy order (order id 529414254) and that order is broken down to fill Account level trades ( trade id's 19581866, 19581867, 19581868). The commission rate should be the same across all trades, but occasionally we see examples where it's not and I'm looking to create a report to display these.
Sample Data:
SQL:
Select a.ACCT_CD, o.ORDER_ID, a.TRADE_ID, s.TICKER, o.TRADE_DATE, a.EXEC_QTY, a.EXEC_PRICE, a.COMMISION_AMT,
a.EXEC_BROKER, a.COMMISION_RATE
from TS_ORDER o join TS_ORDER_ALLOC a on o.ORDER_ID = a.ORDER_ID
join CSM_SECURITY s on o.sec_id = s.sec_id
join CS_FUND f on a.ACCT_CD = f.ACCT_CD
Where o.ORDER_ID = 529414254
Notes: the TS_Order table contains the order level data and TS_ORDER_ALLOC contains the trade level. Any idea how to pull back Orders where the different commission rates are used?
;WITH cte
AS (SELECT a.acct_cd,
o.order_id,
a.trade_id,
s.ticker,
o.trade_date,
a.exec_qty,
a.exec_price,
a.commision_amt,
a.exec_broker,
a.commision_rate
FROM ts_order o
JOIN ts_order_alloc a
ON o.order_id = a.order_id
JOIN csm_security s
ON o.sec_id = s.sec_id
JOIN cs_fund f
ON a.acct_cd = f.acct_cd)
SELECT order_id,
Count(DISTINCT commision_rate)
FROM cte
HAVING Count(DISTINCT commision_rate) > 1
;with cte_orders_with_multi_comm_rates as (
Select o.ORDER_ID
From TS_ORDER o
join TS_ORDER_ALLOC a on o.ORDER_ID = a.ORDER_ID
Group By o.ORDER_ID
Having Count(Distinct a.Commission_rate) > 1
)
select a.ACCT_CD, o.ORDER_ID, a.TRADE_ID, s.TICKER,
o.TRADE_DATE, a.EXEC_QTY, a.EXEC_PRICE, a.COMMISION_AMT,
a.EXEC_BROKER, a.COMMISION_RATE
from cte_orders_with_multi_comm_rates ow
join TS_ORDER o on o.ORDER_ID = ow.ORDER_ID
join TS_ORDER_ALLOC a on o.ORDER_ID = a.ORDER_ID
join CSM_SECURITY s on o.sec_id = s.sec_id
join CS_FUND f on a.ACCT_CD = f.ACCT_CD