select
row_number() over (PARTITION BY b.patient_id,b.visit_id order by b.patient_id,b.visit_id) as seq_num,
b.patient_id,b.visit_id,b.dept_stayed,b.admission_date_time,b.discharge_date_time,b.dept_transfered_to,b.doctor_in_charge
from
transfer b order by b.patient_id,b.visit_id,b.admission_date_time
01-11
5466
11-03
3366
12-04
1467
06-13
904
09-01
1519
09-28
869