Find Consecutive Date in SQL using 2 date columns

Question

I am trying to group by all consecutive patient admission to the table. If the patient re-admitted to the hospital next day( or within 24 hours), then I need to keep only one admission stay per patient. I appreciate your help.

0

Patient_bed_id	Patientname	Hospital_In_date	Hospital_out_Date
111	Lukas	1/1/2022	1/31/2022
111	Lukas	2/1/2022	2/28/2022
111	Lukas	3/1/2022	3/31/2022
111	Lukas	5/25/2022	6/2/2022
111	Lukas	8/1/2022	8/20/2022
111	Lukas	8/21/2022	9/10/2022
222	Jason	5/1/2022	5/3/2022
222	Jason	6/15/2022	7/11/2022
222	Jason	7/12/2022	7/26/2022
222	Jason	9/13/2022	9/15/2022
222	Jason	9/16/2022	9/27/2022

Final table

Patient_bed_id	Patientname	Hospital_In_date	Hospital_out_Date
111	Lukas	1/1/2022	3/31/2022
111	Lukas	5/25/2022	6/2/2022
111	Lukas	8/1/2022	9/10/2022
222	Jason	5/1/2022	5/3/2022
222	Jason	6/15/2022	7/26/2022
222	Jason	9/13/2022	9/27/2022

Answer 1

WITH
cte1 AS (
    SELECT *,
           COALESCE(Hospital_In_date <> LAG(Hospital_out_Date) OVER (PARTITION BY Patient_bed_id, Patientname ORDER BY Hospital_In_date) + INTERVAL 1 DAY, 0) not_consecutive
    FROM test
), 
cte2 AS (
    SELECT *, 
           SUM(not_consecutive) OVER (PARTITION BY Patient_bed_id, Patientname ORDER BY Hospital_In_date) group_no
    FROM cte1
)
SELECT Patient_bed_id,
       Patientname,
       MIN(Hospital_In_date) Hospital_In_date,
       MAX(Hospital_out_Date) Hospital_out_Date
FROM cte2
GROUP BY Patient_bed_id, 
         Patientname, 
         group_no;

fiddle, step-by-step.