Rolling Net Trade Values up by month

1

Hi, I'm working with this sql that provides net trade dollar value for each trade date. How can I roll this up to calculate and display net trades by month instead of by day.
Current Display
|TRADE_DATE | Net Amount|
|01/02/2019 | 8265735.51| |01/03/2019 | -131808598.17|
|01/04/2019 | $49053573.64|
etc..

Desired Display
Jan 2019 | 300446390.7 Feb 2019 | -544621.80

SQL
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
Thanks.

2

Can you provide ddl and sample data? your example doesn't even add up to your desired results. The query doesn't help either since we don't have your schema

1 Like