How many days patient was in ICU

Not sure, but maybe the following does what you want

;With cte As
(Select patent_id, visit_id, [name of PR], Transfer, 
  Row_Number() Over (Partition By patent_id, visit_id Order By Transfer) As rn
From @Tbl
Union All
Select patent_id, visit_id, 'Not ICU', Min(service_start), 0
From @Tbl
Group By patent_id, visit_id)
Select c1.patent_id, c1.visit_id, 
  Sum(DateDiff(day, c1.Transfer, c2.Transfer)) + Count(*) As DaysInICU
From cte c1
Inner Join cte c2 On c1.patent_id = c2.patent_id 
  And c1.visit_id = c2.visit_id 
  And c1.rn = c2.rn - 1
Where c2.[name of PR] = 'ICU'
Group By c1.patent_id, c1.visit_id
Order By c1.patent_id, c1.visit_id;

Tom