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 |
CodePudding user response：
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