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)
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,
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.
This is unclear to me as to what you want. Can you provide DDL and sample data with expected output?
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)
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.