SQLTeam.com | Weblogs | Forums

Summarize Case Stament using MS SQL Server


#1

Hi, the sql as written is wanting me to group by Broker_Reason as well as Comments (which is the broker's name). I only want to summarize by Comments. I've attempted to max and min for Broker_Reason and for the entire case statement, but not getting results I'm after. Can you tell me how to summarize commission at the Broker level without breaking out by broker reason codes as well?

Select (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end) 'Broker',
sum(a.PRIN_SETTLE_FX_RATEa.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 b.COMMENTS
Having sum(a.PRIN_SETTLE_FX_RATE
a.COMMISION_AMT) is not null


#2

group by (Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 'Different Broker' else (b.COMMENTS) end)


#3

bitsmed..thank you. At first it was still giving me two results for broker by reason code but it appears the I took out my Having statement and it looks right now. That was a simple solution, but I wouldn't have thought to group by my case statement, so thanks again.


#4

Hi, I was given another requirement for Broker commission report. Anytime BrokerX is used for b.COMMENTS and a.BROKER_REASON = 4 then commission (a.PRIN_SETTLE_FX_RATE *a.COMMISION_AMT) is split. 80% of commission goes to BrokerX and remainder would go to a.EXEC_BROKER. This arrangement will be unique to BrokerX so hardcoding like with case statement isn't an issue.
I do not think this can be accomplished with another case statement, but hoping you can provide some direction. Let me know if I should repost as new topic.


#5

Select
COMM = Case when (b.COMMENTS) = 'BrokerX' and (a.BROKER_REASON) = '4' then 80/100 *(a.PRIN_SETTLE_FX_RATE * a.COMMISION_AMT)
when a.EXEC_BROKER then (a.PRIN_SETTLE_FX_RATE * a.COMMISION_AMT)-80/100 *(a.PRIN_SETTLE_FX_RATE * a.COMMISION_AMT) END
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 b.COMMENTS
Having sum(a.PRIN_SETTLE_FX_RATE a.COMMISION_AMT) is not nuLL