R12 HCM merge assignment periods with the same status (Active/Leave)?
I'm looking for something that concatenates consecutive date ranges from per_all_assignments_f if the assignment_status_type_id has the same value and the effective_start_date and effective_end_dates are consecutive for each person_id.
For example:
Assignment ID Effective Start Date Effective_end Date assignment_status_type_id
1234 1/1/2020 1/15/2020 1
1234 1/16/2020 1/31/2020 1
1234 2/1/2020 2/20/2020 2
1234 3/1/2020 3/10/2020 1
1234 3/11/2020 12/31/4712 1
Would be returned as:
Assignment ID Effective Start Date Effective_end Date assignment_status_type_id
1234 1/1/2020 1/31/2020 1
1234 2/1/2020 2/20/2020 2
1234 3/1/2020 12/31/4712 1