How to turn Time Ranking data into a "Timeline" with T-SQL

I have a set of data below

        Agent   Rank    START   STOP    CODE
        Joey    52      11:30   11:45   BRK_Break1
        Joey    53      17:30   17:45   BRK_Break2
    开发者_运维百科    Joey    57      14:15   15:15   BRK_Lunch
        Joey    152     09:40   19:00   CONT_Shift

which is a persons "status" throughout the data. The logic needs to be that there is one line for each "period" of time so it creates one contiunal "timeline" based upon the rank (lower = higher priority) so it looks like below

Agent   Start   Stop    Code
Joey    09:40   11:30   CONT_Shift
Joey    11:30   11:45   BRK_Break1
Joey    11:45   14:15   CONT_Shift
Joey    14:15   15:15   BRK_Lunch
Joey    15:15   17:30   CONT_Shift
Joey    17:30   17:45   BRK_Break2
Joey    17:45   19:00   CONT_Shift

Any ideas how this can be achived? Ideally I would like to restrict the use of staging tables and do this through a CTE or maybe some self joins but not sure where to start?

---

It is a really good question, answering it was quite hard. first I waited for someone else to solve it, but since that didn't happen, I gave it a try, I discovered a mistake and gave it another try.

it is not pretty, but it seems there are no features in sql supporting the question. So the sql is quite complex. If someone else comes up with a different and better solution, I will be the first to give it a plus.

declare @t table (name varchar(10), rank int, start datetime, stop datetime, code varchar(12))
insert @t values ('Joey', 52, '2011-06-21 11:30', '2011-06-21 11:45', 'BRK_Break1')     
insert @t values ('Joey', 53, '2011-06-21 17:30', '2011-06-21 17:45', 'BRK_Break2')         
insert @t values ('Joey', 57, '2011-06-21 14:15', '2011-06-21 15:15', 'BRK_Lunch')     
insert @t values ('Joey',152, '2011-06-21 09:40', '2011-06-21 19:00', 'CONT_Shift')
insert @t values ('Joey',152, '2011-06-22 09:40', '2011-06-22 19:00', 'CONT_Shift')

;with aa as
(
select name, rank, start, 'b' action, code from @t
union all
select name, rank, stop,  'e', code from @t
)
select * from (
select name,start, 
(select min(start) from aa where start > a.start and a.name = name) stop, 
(select code from (select rank() OVER (ORDER BY rank) as rank, code from @t where dateadd(second, 1, a.start) between start and stop and name = a.name) c where rank = 1) code
from aa a
where not exists (select 1 from @t where a.start between start and stop and a.rank > rank and a.name = name)
and exists (select 1 from @t where a.start between start and stop and a.name = name)
) d
where code is not null and 
name = 'Joey'
order by start