Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TEMP TABLE tbl (id int, identifier int,  admission_date date, release_date date);

SET datestyle = 'ISO,MDY';  -- to deal with your local date format

INSERT INTO tbl VALUES
  (234, 2, '5/1/22 ', '5/5/22')
, (234, 1, '4/25/22', '4/30/22')
, (234, 2, '4/20/22', '4/24/22')
, (234, 2, '4/15/22', '4/18/22')
, (789, 1, '7/15/22', '7/19/22')
, (789, 2, '7/8/22' , '7/14/22')
, (789, 2, '7/1/22' , '7/5/22' )
, (321, 2, '6/1/21' , '6/3/21' )
, (321, 2, '5/27/21', '5/31/21')
, (321, 1, '5/20/21', '5/26/21')
, (321, 2, '5/15/21', '5/19/21')
, (321, 2, '5/6/21' , '5/10/21')
;

RESET datestyle;  -- back to proper ISO format

CREATE TABLE



SET



INSERT 0 12



RESET


SELECT (x.my_row).*
FROM  (
   SELECT identifier
        , lag(t)  OVER w AS t0
        , t              AS t1
        , lead(t) OVER w AS t2
   FROM   tbl t
   WINDOW w AS (PARTITION BY id ORDER BY admission_date)
   ) sub
CROSS JOIN LATERAL (
   VALUES (t0), (t1), (t2)
    ) x(my_row)
WHERE  sub.identifier = 1
AND    (x.my_row).id IS NOT NULL;


id
identifier
admission_date
release_date


234
2
2022-04-20
2022-04-24

234
1
2022-04-25
2022-04-30

234
2
2022-05-01
2022-05-05

321
2
2021-05-15
2021-05-19

321
1
2021-05-20
2021-05-26

321
2
2021-05-27
2021-05-31

789
2
2022-07-08
2022-07-14

789
1
2022-07-15
2022-07-19



SELECT 8


with d as (
  select *, 
    case when identifier = 1 then Lag(admission_date) over(partition by id order by Admission_Date desc) end pd, 
    case when identifier = 1 then Lead(admission_date) over(partition by id order by Admission_Date desc) end nd
  from tbl
)
select id, Identifier, Admission_Date, Release_Date
from d
where identifier = 1
or exists (
  select * from d d2 
  where d2.id = d.id
    and (d.Admission_Date = pd or d.admission_date = nd)
)
order by Id, Admission_Date desc;


id
identifier
admission_date
release_date


234
2
2022-05-01
2022-05-05

234
1
2022-04-25
2022-04-30

234
2
2022-04-20
2022-04-24

321
2
2021-05-27
2021-05-31

321
1
2021-05-20
2021-05-26

321
2
2021-05-15
2021-05-19

789
1
2022-07-15
2022-07-19

789
2
2022-07-08
2022-07-14



SELECT 8

id	identifier	admission_date	release_date
234	2	2022-04-20	2022-04-24
234	1	2022-04-25	2022-04-30
234	2	2022-05-01	2022-05-05
321	2	2021-05-15	2021-05-19
321	1	2021-05-20	2021-05-26
321	2	2021-05-27	2021-05-31
789	2	2022-07-08	2022-07-14
789	1	2022-07-15	2022-07-19