How to run cumulative totals by date with multiple groupings

user_17694264 0
Viewed 54

I have several year's worth of daily profits for different portfolios and strategies. For every date, portfolio and strategy I'd like to see the daily profit (not calculated as this is already available), as well as the MTD, QTD, YTD and LTD values. I had accomplished this with some nested while loops but takes forever and I'm fairly confident this could be accomplished with some joins. If someone could walk me through it would be greatly appreciated. A simplified version of the exercise is below. With the first 4 columns already available, and the right for are what I'm looking to accomplish:

Date GROUP_1 GROUP_2 Daily Profits MTD QTD YTD LTD
2021-01-15 A X 1 1 1 1 1
2021-01-19 A X 5 6 6 6 6
2021-01-20 A X 2 8 8 8 8
2021-01-24 A X 5 13 13 13 13
2021-02-19 A X 5 5 18 18 18
2021-02-23 A Y 4 4 4 4 4
2021-03-21 A X 1 1 19 19 19
2021-03-25 A Y 5 5 9 9 9
2021-04-20 A X 5 5 5 24 24
2021-04-24 A Y 2 2 2 11 11
2021-05-20 A X 3 3 8 27 27
2021-05-24 A Y 4 4 6 15 15
2021-01-15 B Y 5 5 5 5 5
2021-01-19 B Y 3 8 8 8 8
2021-01-20 B Y 1 9 9 9 9
2021-01-24 B Y 2 11 11 11 11
2021-02-19 B Y 4 4 15 15 15
2021-02-23 B Y 5 9 20 20 20
2021-03-21 B Y 1 1 21 21 21
2021-03-25 B Y 1 2 22 22 22
2021-04-20 B Y 4 4 4 26 26
2021-04-24 B Y 5 9 9 31 31
2021-05-20 B Y 1 1 10 32 32
2021-05-24 B Y 2 3 12 34 34
sql-server-2017 tsql sql-server sql
1 Answers

Something like the following SQL script should do the trick:

select 
  value_date,
  group_1,
  group_2,
  daily_profits,
  sum(daily_profits) over (partition by datepart(yy, value_date), datepart(mm, value_date), group_1, group_2 order by value_date, group_1, group_2 rows unbounded preceding) as MTD,
  sum(daily_profits) over (partition by datepart(yy, value_date), datepart(qq, value_date), group_1, group_2 order by value_date, group_1, group_2 rows unbounded preceding) as QTD,
  sum(daily_profits) over (partition by datepart(yy, value_date), group_1, group_2 order by value_date, group_1, group_2 rows unbounded preceding) as YTD,
  sum(daily_profits) over (partition by group_1, group_2 order by value_date, group_1, group_2 rows unbounded preceding) as LTD
from agg_profit
order by value_date, group_1, group_2

SQL Fiddle for reference: http://sqlfiddle.com/#!18/d6c5f/1

Documentation is found here: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

user_123480980
Related