Oracle 21c

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

CREATE TABLE CASE_DEPARTMENTS(
  STAY_ID NUMBER GENERATED by default on null as IDENTITY,
  BEGINNING TIMESTAMP,
  ENDING TIMESTAMP,
  DEPARTMENT VARCHAR2(50),
  ROOM VARCHAR2(50) NULL,
  BED VARCHAR2(50) NULL
)
INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('13-08-2021 10:42:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('13-08-2021 14:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM', 
        '123', 
        '456');
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('13-08-2021 14:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('15-08-2021 09:22:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM', 
        '345', 
        '789');
  
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT, ROOM, BED) 
VALUES (TO_DATE('15-08-2021 09:22:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('28-08-2021 21:09:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'ACH', 
        '111', 
        '222');
1 rows affected

INSERT INTO CASE_DEPARTMENTS (BEGINNING, ENDING, DEPARTMENT) 
VALUES (TO_DATE('28-08-2021 21:09:00', 'DD-MM-YYYY HH24:MI:SS'), 
        TO_DATE('01-09-2021 13:34:00', 'DD-MM-YYYY HH24:MI:SS'), 
        'HAEM');
1 rows affected

select
  grp,
  max(department) as department,
  min(beginning) as beginning,
  max(ending) as ending,
  max(ending) - min(beginning) as duration
from (
  select x.*,
    sum(inc) over(order by beginning) as grp
  from (
    select d.*,
    lag(ending) over(order by beginning) as x,
    lag(department) over(order by beginning) as y,
      case when beginning = lag(ending) over(order by beginning)
            and department = lag(department) over(order by beginning)
        then 0 else 1 end as inc
    from case_departments d
  ) x
) y
group by grp


GRP
DEPARTMENT
BEGINNING
ENDING
DURATION


1
HAEM
13-AUG-21 10.42.00.000000
15-AUG-21 09.22.00.000000
+000000001 22:40:00.000000

2
ACH
15-AUG-21 09.22.00.000000
28-AUG-21 21.09.00.000000
+000000013 11:47:00.000000

3
HAEM
28-AUG-21 21.09.00.000000
01-SEP-21 13.34.00.000000
+000000003 16:25:00.000000

GRP	DEPARTMENT	BEGINNING	ENDING	DURATION
1	HAEM	13-AUG-21 10.42.00.000000	15-AUG-21 09.22.00.000000	+000000001 22:40:00.000000
2	ACH	15-AUG-21 09.22.00.000000	28-AUG-21 21.09.00.000000	+000000013 11:47:00.000000
3	HAEM	28-AUG-21 21.09.00.000000	01-SEP-21 13.34.00.000000	+000000003 16:25:00.000000