Get order of records within a group
I have a situation something like this:
Employee ID | Effective Date | Office |
1 | 1/1/2023 | San Francisco |
1 | 1/1/2024 | Oakland |
2 | 6/1/2023 | Sydney |
2 | 12/1/2023 | Tokyo |
I need to add an order column and end date of the previous record column. Can you please suggest any ways of doing this? The output should probably look like this:
| Employee ID | Effective Date | Office | End Date | Order |
| 1 | 1/1/2023 | San Francisco | 12/31/2023 | 1 |
| 1 | 1/1/2024 | Oakland | 2 | |
| 2 | 6/1/2023 | Sydney | 11/30/2023 | 1 |
| 2 | 12/1/2023 | Tokyo | 2 |
Best,
Rajesh
