I am trying to consolidate rows of data that have consecutive dates into a single row with a start date from the first row and an end date from the second row. The data is pretty messy. There are two potential statuses - positive and resolved. I only want to show the window(s) when a person is positive which starts when they have a positive status and ends when they have a resolved status. What makes it challenging is that a person can have several positive rows or several resolved rows in succession. Here is a sample of the data.
WITH ddata AS (
SELECT '1234' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('12/20/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/12/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Positive' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('01/13/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/17/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Positive' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('01/18/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('02/14/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('02/15/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('02/20/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Positive' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('02/21/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('03/22/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('03/23/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('06/06/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('06/07/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/03/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '1234' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('01/04/2023','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/11/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual UNION ALL
SELECT '2345' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('08/03/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('08/03/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Positive' AS next_status FROM dual UNION ALL
SELECT '2345' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('08/04/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('09/01/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '2345' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('09/02/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/11/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual UNION ALL
SELECT '3456' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('03/31/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('04/24/2021' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '3456' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('04/25/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('12/23/2021' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual UNION ALL
SELECT '3456' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('12/24/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/28/2022' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '3456' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('01/29/2022','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/11/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual UNION ALL
SELECT '4567' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('05/31/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('06/24/2021' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, 'Resolved' AS next_status FROM dual UNION ALL
SELECT '4567' AS person_id, 'Resolved' AS ID_STATUS, TO_DATE('06/24/2021','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/11/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual UNION ALL
SELECT '5678' AS person_id, 'Positive' AS ID_STATUS, TO_DATE('01/02/2023','mm/dd/yyyy') AS STATUS_DATE, TO_DATE('01/11/2023' , 'mm/dd/yyyy') AS NEXT_STATUS_DATE, NULL AS next_status FROM dual
)
SELECT * FROM ddata order by person_id, status_date
PERSON_ID ID_STATUS STATUS_DATE NEXT_STATUS_DATE NEXT_STATUS
1234 Positive 12/20/2021 01/12/2022 Positive
1234 Positive 01/13/2022 01/17/2022 Positive
1234 Positive 01/18/2022 02/14/2022 Resolved
1234 Resolved 02/15/2022 02/20/2022 Positive
1234 Positive 02/21/2022 03/22/2022 Resolved
1234 Resolved 03/23/2022 06/06/2022 Resolved
1234 Resolved 06/07/2022 01/03/2023 Resolved
1234 Resolved 01/04/2023 01/11/2023
2345 Positive 08/03/2022 08/03/2022 Positive
2345 Positive 08/04/2022 09/01/2022 Resolved
2345 Resolved 09/02/2022 01/11/2023
3456 Positive 03/31/2021 04/24/2021 Resolved
3456 Resolved 04/25/2021 12/23/2021
3456 Positive 12/24/2021 01/28/2022 Resolved
3456 Resolved 01/29/2022 01/11/2023
4567 Positive 05/31/2021 06/24/2021 Resolved
4567 Resolved 06/24/2021 01/11/2023
5678 Positive 01/02/2023 01/11/2023
Only positive rows will be displayed and any successive positive rows should be consolidated into a single row. The data above should ultimately look like this:
person_id status status_date end_date
1234 positive 12/20/2021 02/14/2022
1234 positive 02/21/2022 03/22/2022
2345 positive 08/03/2022 09/01/2022
3456 positive 03/31/2021 04/24/2021
3456 positive 12/24/2021 01/28/2022
4567 positive 05/31/2021 06/24/2021
5678 positive 01/02/2023 01/11/2023
I am on 19c. Any thoughts on how do accomplish this would be greatly appreciated.
-Kevin