[SQL] How can I separate RowNumber counting?

I'm trying to create a point in time hierarchy in my organization. The problem I'm running into are some folks have reported to the same manager more than once in their career. I tried doing a row_number on their ID and manager, but as expected, the counting continues when they report back to the same manager. Some sample data:

ID​

Manager ID​

EFF_DT​

EXP_DT​

RWNUM
1​

2​

5/24/2020​

6/30/2020​

1
1​

2​

7/1/20202​

8/25/2020​

2
1​

2​

8/26/2020​

12/9/2020​

3
1​

3​

12/10/2020​

1/29/2021​

1
1​

3​

1/30/2021​

5/30/2021​

2
1​

3​

5/31/2021​

7/15/2021​

3
1​

4​

7/16/2021​

8/30/2021​

1
1​

4​

9/01/2021​

9/15/2021​

1
1​

2​

9/16/2021​

12/31/2021​

4
1​

2​

1/1/2022​

3/31/2022​

5​

So when this employee reported back to manager 2, the rownumber counted to 4 and 5. Is there a way in SQL to have it reset and count as 1 and 2?
Sample of the desired end data:

ID​

Manager ID​

EFF_DT​

EXP_DT
1​

2​

5/24/2020​

12/9/2020
1​

3​

12/10/2020​

7/15/2021
1​

4​

7/16/2021​

9/15/2021
1​

2​

9/16/2021​

3/31/2022​

I figured the best way was to use window functions, but I can't figure out a simple way to get this result. All suggestions are welcome, thank you!

I created the tables wrong, and went to edit them. Stackoverflow autosaved the edit when I finished the 1st one, and wouldn't let me save this edit on the 2nd table until I made the body thoroughly different, so I'm adding this paragraph to hopefully satisfy that requirement.

Continue reading...