Hi, I'm trying to calculate a Net Trade Amount by adding Buys and (Sells) for each trade date. I thought I could simply add the two case statement together but MS SMS V17.9 doesn't like the + sign. Any Ideas? This will likely be a two part question. Ultimately I'll want to roll this up to calculate net trade amount by Month but figured I'd start with daily trades. Any help will be appreciated. Thanks.
Select convert(varchar(10),o.TRADE_DATE,101) 'TRADE_DATE',
Case when o.TRANS_TYPE = 'SELLL' then Sum(a.PRIN_SETTLE_FX_RATEa.EXEC_AMT-1) +
when o.TRANS_TYPE = 'BUYL' then Sum(a.PRIN_SETTLE_FX_RATE*a.EXEC_AMT) else null end 'Net Amount'
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
where o.TRADE_DATE >= '01/01/2019' and o.TRADE_DATE <= '09/01/2020'
and s.SEC_TYP_CD in ('FSTK') and o.EXCH_CD not in ('NYS','NAS')
and o.STATUS = 'ACCT'
Group by o.TRADE_DATE, o.TRANS_TYPE
Output should just display Trade Date and Net Amount.