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.
If you are wanting to collapse contiguous rows into a single row, you can use Teradata normalize function. It only works with periods, so we have to convert your dates into a period.
select
id,
manager_id,
begin(prd) as eff_dt,
last(prd) as exp_dt
from (
select normalize
id,
manager_id,
period(eff_dt,exp_dt + 1) as prd
from
<your table>) t