How to create an ID column based on condition of previous row?

I am trying to group hospital admission transfers, with the initial index admission. So far I have been able to categorize each admission as ‘index’ or ‘transfer’ based on the time since previous discharge. Now I am trying to group together the admissions together that are in the same episode of care (e.g. each index admission and its associated transfers).

I have tried creating an episode_id field to group transfers with the appropriate index admission. I am trying to set episode_id to sort by uli (individual identifier) and admitdate_dt (admission date), and then run through each row. I want to have episode_id = previous episode_id if admission_type is transfer. If admission_type is index, I want episode_id to be equal to previous episode_id+ 1. The problem with the code below (I think) is it is not looping through each row individually, but running all rows at once with current value of NULL for all rows except the first, which I manually set. How can I write the code to loop through each row sequentially and get the desired output below?

ALTER TABLE dad_index_admissions_2
ADD episode_id INT; -- Creating an episode id field
    
UPDATE admissions
SET episode_id = 1
WHERE seqnum = (SELECT seqnum FROM admissions ORDER BY uli, admitdate_dt FETCH NEXT 1 ROWS ONLY);
-- Setting the first row with an episode_id of 1. Seqnum is primary key for admissions table. ULI is individual identifier.

SELECT CASE WHEN episode_id = 1 THEN 1 WHEN admission_type = 'transfer' THEN (LAG(episode_id) OVER(PARTITION BY uli ORDER BY uli, admitdate_dt))
WHEN admission_type = 'index' THEN ((LAG(episode_id+1) OVER(ORDER BY uli, admitdate_dt)))
END AS episode_id, admitdate_dt, disdate_dt, uli, admission_type
FROM admissions; -- My attempt to generate values for episode_id. 

Example Of Desired Output:

episode_id	admitdate_dt	disdate_dt	uli	admission_type
1	01 – JAN – 2022	02- JAN – 2022	001	index
1	02 – JAN – 2022	10- JAN – 2022	001	transfer
1	10 – JAN – 2022	20- JAN – 2022	001	transfer
2	01 – JAN – 2022	05- JAN – 2022	002	index
3	10 – JAN – 2022	20- JAN – 2022	002	index
3	20 – JAN – 2022	25- JAN – 2022	002	transfer

Answer

Count the number of index rows:

SELECT COUNT(CASE admission_type WHEN 'index' THEN 1 END)
         OVER (ORDER BY uli, admitdate_dt)
         AS episode_id,
       admitdate_dt,
       disdate_dt,
       uli,
       admission_type
FROM   admissions;

Which, for the sample data:

CREATE TABLE admissions (admitdate_dt, disdate_dt, uli, admission_type) AS
SELECT DATE '2022-01-01', DATE '2022-01-02', '001', 'index' FROM DUAL UNION ALL
SELECT DATE '2022-01-02', DATE '2022-01-10', '001', 'transfer' FROM DUAL UNION ALL
SELECT DATE '2022-01-10', DATE '2022-01-20', '001', 'transfer' FROM DUAL UNION ALL
SELECT DATE '2022-01-01', DATE '2022-01-05', '002', 'index' FROM DUAL UNION ALL
SELECT DATE '2022-01-10', DATE '2022-01-20', '002', 'index' FROM DUAL UNION ALL
SELECT DATE '2022-01-20', DATE '2022-01-25', '002', 'transfer' FROM DUAL;

Outputs:

EPISODE_ID	ADMITDATE_DT	DISDATE_DT	ULI	ADMISSION_TYPE
1	01-JAN-22	02-JAN-22	001	index
1	02-JAN-22	10-JAN-22	001	transfer
1	10-JAN-22	20-JAN-22	001	transfer
2	01-JAN-22	05-JAN-22	002	index
3	10-JAN-22	20-JAN-22	002	index
3	20-JAN-22	25-JAN-22	002	transfer

db<>fiddle here