SQLTeam.com | Weblogs | Forums

Commission Split for Broker report


#1

Hello, I was given a new requirement for a commission report I'm working on and had received help from here earlier, so coming back for more. The first requirement is to return a broker name when broker reason code 4 is used. The New requirement is to break out commission for an 80/20 split between the Comment broker (b.COMMENTS) and the executing broker (a.EXEC_BROKER. ) I'm not sure how to accomplish that with a case statement, or if case statement is the best way to handle. Any ideas. The report will be a summation and grouped by Broker, so just two columns Broker Name | Commission.
Here is what I'm working with.
Select (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end) 'Broker',
sum(a.PRIN_SETTLE_FX_RATE *a.COMMISION_AMT) 'COMM'
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_BROKER b on b.BKR_CD = COALESCE(a.BKR_OF_CREDIT, a.STEP_OUT_BROKER, a.EXEC_BROKER)
where o.TRADE_DATE >= '1/1/2019' and o.trade_date <= '1/08/2019' and o.STATUS = 'acct'
and s.SEC_TYP_CD in ('ADR','COM','FSTK')
Group by (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end)


#2

The query you posted has a number of syntax errors, so it is hard to discern what you are attempting to do. For example what are you trying to sum up in the following snippet?

SUM(a.PRIN_SETTLE_FX_RATE a.COMMISION_AMT)

If you are trying to sum up COMMISSION_AMT converted to USD, then it should be

SUM(a.PRIN_SETTLE_FX_RATE * a.COMMISION_AMT)

That is just an example. If you are trying to alias a column the alias should be in square brackets or double quotes, but not in single quotes. For example, any of the following 3 are okay.

(CASE WHEN (b.COMMENTS) = 'BrokerX' AND (a.BROKER_REASON) = '4' THEN 'Different Broker' ELSE (b.COMMENTS) END) "Broker",

or

(CASE WHEN (b.COMMENTS) = 'BrokerX' AND (a.BROKER_REASON) = '4' THEN 'Different Broker' ELSE (b.COMMENTS) END) [Broker],

or

(CASE WHEN (b.COMMENTS) = 'BrokerX' AND (a.BROKER_REASON) = '4' THEN 'Different Broker' ELSE (b.COMMENTS) END) Broker,


#3

Thanks, you're right, it should be SUM(a.PRIN_SETTLE_FX_RATE * a.COMMISION_AMT) not sure what became of the *
I'm using MS SQL server 2012, and it like single ticks or no ticks around column alias..I use single ticks out of habit. Essentially what I'm attempting to do is split a commission when a specific broker is used and it's on foreign trades using broker reason code 4. 80% will go to the b.COMMENTS broker and the other 20% will go to the a.EXEC_BROKER and it's commission need to be summarized and grouped by broker.


#4

This is unclear to me as to what you want. Can you provide DDL and sample data with expected output?


#5
Select (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end) 'Broker',
 sum(a.PRIN_SETTLE_FX_RATE *a.COMMISION_AMT) 'COMM',
 case when a.broker_reason='4' then b.comments else null end as 'broker1',
 sum(case when a.broker_reason='4' then a.prin_settle_fx_rate*a.commision_amt*.8 else 0 end 'comm1',
 case when a.broker_reason='4' then a.exec_broker else null end as 'broker2'
 sum(case when a.broker_reason='4' then a.prin_settle_fx_rate*a.commision_amt*.2 else 0 end 'comm2',
 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_BROKER b on b.BKR_CD = COALESCE(a.BKR_OF_CREDIT, a.STEP_OUT_BROKER, a.EXEC_BROKER)
 where o.TRADE_DATE >= '1/1/2019' and o.trade_date <= '1/08/2019' and o.STATUS = 'acct'
 and s.SEC_TYP_CD in ('ADR','COM','FSTK')
 Group by (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end)

#6

This is definitely getting me in the right direction, which is remarkable given what I provided you to work with. Thanks again. I'm getting close, but may have a couple more questions to tweak it.