How many days patient was in ICU

Try the below query,

------------------------- final query
select distinct patent_id, visit_id, dbo.getICUHour(patent_id, visit_id) as ICUHour from serviceTable
------------------------- user define function
create function getICUHour(@patent_id int, @visit_id int)
returns int
begin
--      set @patent_id = 3
--      set @visit_id = 400
        declare @prname nvarchar(20),@transfer datetime,@serviceStart datetime,@serviceEnd datetime
        declare @dayICU int,@previousTransfer datetime
        declare c cursor for select [name of PR],[transfer],[service_start],[service_end] from serviceTable 
                                                where patent_id=@patent_id and visit_id=@visit_id and datediff(d,transfer,service_end)>=0 order by [transfer]
                         
        open c
        fetch next from c into @prname,@transfer,@serviceStart,@serviceEnd
        set @dayICU=0
        set @previousTransfer = null
        while @@fetch_status = 0
        begin
                if @prname = 'ICU'
                begin
                        if @serviceStart <= @previousTransfer set @serviceStart = @previousTransfer
                        else  if @previousTransfer is null set @serviceStart = @serviceStart
                        set @dayICU = @dayICU + datediff(d,@serviceStart,@transfer) + 1
                end
                else
                begin
                        set @previousTransfer = @transfer
                        set @dayICU = @dayICU 
                end
                fetch next from c into @prname,@transfer,@serviceStart,@serviceEnd
        end
        close c
        deallocate c
        return @dayICU
end

---------- table definition
create table serviceTable  
(patent_id int, visit_id int, [name of PR] nvarchar(100),Transfer datetime, service_start datetime,service_end datetime)
insert into serviceTable values(1,100,'Not ICU','02/05/2013','02/05/2013','02/08/2013')
insert into serviceTable values(1,100,'ICU','02/06/2013','02/05/2013','02/08/2013')
insert into serviceTable values(1,100,'Not ICU','02/08/2013','02/05/2013','02/08/2013')
insert into serviceTable values(2,200,'Not ICU','03/11/2013','03/10/2013','03/15/2013')
insert into serviceTable values(2,200,'ICU','03/15/2013','03/10/2013','03/15/2013')
insert into serviceTable values(3,400,'ICU','10/15/2013','10/15/2013','10/20/2013')
insert into serviceTable values(3,400,'Not ICU','10/18/2013','10/15/2013','10/20/2013')
insert into serviceTable values(3,400,'ICU','10/20/2013','10/15/2013','10/20/2013')
------------------------

---

Regards, RSingh