SQLTeam.com | Weblogs | Forums

SQL to compare column values

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?

hi

hope this helps :slight_smile: :slight_smile:

;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

This is on the right track, but for report purposes I need the trade details and this appears to give count. Thanks for taking time to post.

Thanks. This appears to nail it and I've never used "with", so this was a big help. Appreciate it.