0

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
1
  • 1
    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!
    – Vérace
    Jun 29, 2022 at 4:44

1 Answer 1

2
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.