SQLite 3.27

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

CREATE TABLE tablename (
  `PID` TEXT,
  `EID` TEXT,
  `EPISODETYPE` TEXT,
  `START_TIME` TEXT,
  `END_TIME` TEXT
);
INSERT INTO tablename
  (`PID`, `EID`, `EPISODETYPE`, `START_TIME`, `END_TIME`)
VALUES
  ('123', '556', 'emergency_room', '2020-03-29 15:09:00', '2020-03-30 20:36:00'),
  ('123', '558', 'ward', '2020-04-30 20:35:00', '2020-05-04 22:12:00'),
  ('123', '660', 'ward', '2020-05-04 22:12:00', '2020-05-21 08:59:00'),
  ('123', '661', 'icu', '2020-05-21 09:00:00', '2020-07-01 17:00:00');
SELECT * FROM tablename


PID
EID
EPISODETYPE
START_TIME
END_TIME


123
556
emergency_room
2020-03-29 15:09:00
2020-03-30 20:36:00

123
558
ward
2020-04-30 20:35:00
2020-05-04 22:12:00

123
660
ward
2020-05-04 22:12:00
2020-05-21 08:59:00

123
661
icu
2020-05-21 09:00:00
2020-07-01 17:00:00


SELECT PID, EID,
       SUM(flag) OVER (PARTITION BY PID ORDER BY START_TIME) StayNumber
FROM (
  SELECT *, 
    strftime('%s', START_TIME) - 
    strftime('%s', LAG(END_TIME, 1, datetime(START_TIME, '-1 hour')) OVER (PARTITION BY PID ORDER BY START_TIME)) > 60 flag
  FROM tablename
)


PID
EID
StayNumber


123
556
1

123
558
2

123
660
2

123
661
2

PID	EID	EPISODETYPE	START_TIME	END_TIME
123	556	emergency_room	2020-03-29 15:09:00	2020-03-30 20:36:00
123	558	ward	2020-04-30 20:35:00	2020-05-04 22:12:00
123	660	ward	2020-05-04 22:12:00	2020-05-21 08:59:00
123	661	icu	2020-05-21 09:00:00	2020-07-01 17:00:00