I have an interesting problem that I am struggling with resolving
Multi row dataset that needs to be combined to produce a start and end times.
Below is sample data with expected results. Any help will be greatly appreciated
Raw Data
Plan ID OTHER_TIME TEAM_NO NAME INSTANT
30686834 4/23/2021 16:00 159 Add 4/20/21 6:49 AM
30686834 4/23/2021 16:00 159 Add 4/20/21 2:03 PM
30686834 4/23/2021 16:00 159 Add 4/20/21 2:03 PM
30686834 4/23/2021 16:00 160 Add /20/21 5:15 PM
30686834 4/23/2021 16:00 160 Remove 4/21/21 7:33 AM
30686834 4/23/2021 16:00 160 Add 4/21/21 7:34 AM
30686834 4/23/2021 16:00 160 Remove 4/21/21 7:34 AM
30778380 5/15/2022 5:22 16 Add 5/10/22 5:22 AM
30778380 5/15/2022 5:22 16 Add 5/10/22 5:23 AM
30698999 7/30/2021 13:35 41 Add 7/18/21 8:29 AM
30698999 7/30/2021 13:35 41 Add 7/19/21 9:18 AM
30698999 7/30/2021 13:35 41 Remove 7/22/21 8:40 AM
30698999 7/30/2021 13:35 158 Add 6/5/21 11:59 AM
30698999 7/30/2021 13:35 158 Remove 6/5/21 12:28 PM
30698999 7/30/2021 13:35 159 Add 6/11/21 7:31 PM
30698999 7/30/2021 13:35 162 Add 6/5/21 12:54 PM
30698999 7/30/2021 13:35 162 Remove 6/11/21 7:31 PM
Rules:
Add Instant = Start Time
Remove Instant = End Time
Use first "Add" instant as start if multiple Add is listed for a plan id and team number. Ignore the susequent Adds
If no remove is listed for a plan id and team number then use the first Add instant as start time and Other Time as end time
A plan id and team number can have multiple adds and multiple removes
Expected Results:
Plan ID TEAM_NO START_TIME END_TIME
30686834 159 4/20/21 6:49 AM 4/23/21 4:00 PM
30686834 160 4/20/21 5:15 PM 4/21/21 7:33 AM
30686834 160 4/21/21 7:34 AM 4/21/21 7:34 AM
30778380 16 5/10/22 5:22 AM 5/15/2022 5:22
30698999 41 7/18/21 8:29 AM 7/22/21 8:40 AM
30698999 158 6/5/21 11:59 AM 6/5/21 12:28 PM
30698999 159 6/11/21 7:31 PM 7/30/2021 13:35
30698999 162 6/5/21 12:54 PM 6/11/21 7:31 PM
What I have tried:
Tried multiple partitioning with Add and Remove but I just can't get the desired results. Also tried lead/lag but given there can be n number of Add and n number of Remove it is not feasible to use these functions