Counting number of days in a date range

[Altamush_Khwaja]

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.

[AkimasaKajitani · Accepted solution]

Here is my solution for you.

In combining the two data streams, I simply combined them by Date.
As a pre-processing, it is needed to split the change date into separate dates.

Input:

I changed this input.

Output:

[Altamush_Khwaja · Accepted solution]

Thank you so much! This works!