Add two case statements to get net trade amount

1

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.

2

NULL will be ignored in a SUM, might as well use 0 instead:

Select convert(varchar(10),o.TRADE_DATE,101) 'TRADE_DATE',
Sum(Case when o.TRANS_TYPE = 'SELLL' then a.PRIN_SETTLE_FX_RATEa.EXEC_AMT-1
         when o.TRANS_TYPE = 'BUYL' then a.PRIN_SETTLE_FX_RATE*a.EXEC_AMT
         else 0 end) 'Net Amount'
1 Like
3

Scott, this is close but it appears to only return the BUYL trades and not the SELLL trades and summing them. Any other ideas?

4

Disregard my last statement, it's returning both Buys and Sells but as separate rows and I'm just wanting one net amount that combines the values.

5

Scott, I'm good. I just removed trans_type from Group by. Thanks for your help.

6

Hi Scott. Now that this summarizes for each day and idea how to get this to roll this up to the Monthly label. So for example, running it for time frame 1/1/19 through 08/31/20 it would display total monthly net trade like:
Month/Year Monthly Net Trade Amt
Jan 2019 $300,446,390.69

Select
convert(varchar(10),o.TRADE_DATE,101) 'TRADE_DATE',
Sum(Case when o.TRANS_TYPE = 'SELLL' then (a.PRIN_SETTLE_FX_RATEa.EXEC_AMT)-1
when o.TRANS_TYPE = 'BUYL' then a.PRIN_SETTLE_FX_RATE*a.EXEC_AMT
else 0 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 <= '01/31/2019'
and s.SEC_TYP_CD in ('FSTK') and o.EXCH_CD not in ('NYS','NAS')
and o.STATUS = 'ACCT'
Group by o.TRADE_DATE