I'm trying to break down start_date end_date combination into a monthly range with an additional wrinkle. The additional step is I need to break down monthly ranges into smaller chunks based on entries in a second table.
So I have a list of records like so
id | start_date | end_date |
1 | 10/15/2020 | 11/08/2020 |
2 | 10/25/2020 | 11/23/2020 |
3 | 11/23/2020 | 12/05/2020 |
I have another table with a list of dates, which represents change in interest rate:
change_date |
10/27/2020 |
12/03/2020 |
Based on this, the time windows for me would be:
Oct_1 - 10/1/2020 - 10/27/2020
Oct_2 - 10/28/2020 - 10/31/2020
Nov - 11/1/2020 - 11/18/2020
Dec_1 - 12/1/2020 - 12/3/2020
Dec_2 - 12/4/2020 - now
So, my output would need to be
id | Oct_1 | Oct_2 | Nov | Dec_1 | Dec_2 |
1 | 12 | 5 | 8 | ||
2 | 2 | 5 | 3 | ||
3 | 8 | 2 | 3 |
Using the excellent linked solution, I was able to get the above to work if the change date happens once a month. I am getting in trouble when trying to generalize to more change dates in a month.
I would appreciate any ideas on how to do that.
Solved! Go to Solution.
Thank you so much! This works!