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

• Vérace: Hi, and welcome to dba.se! Please go to dbfiddle.uk and construct a fiddle there with your own server. By presenting your data as you have, you are basically asking us to type (a lot) of stuff into our systems - an error prone step. A fiddle can be a single source of truth for the question and eliminates duplication of effort on behalf of those trying to answer your question - help us to help you!

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.