SQL Server : remove redundant rows where same room in sequence -

have data tracks person movements through beds, e.g

personsk   arrivaldttm          room    sequence  ------------------------------------------------ 11111      01/01/2015 15:00     bed 1       1 11111      01/01/2015 18:00     bed 1       2 11111      01/01/2015 21:00     bed 1       3 11111      01/01/2015 22:00     bed 7       4 

desired output is

personsk   arrivaldttm          room    sequence  departure dttm ---------------------------------------------------------------- 11111      01/01/2015 15:00     bed 1       1     01/01/2015 22:00 11111      01/01/2015 22:00     bed 7       2     null 

can't think of way this, logic want impose following:

• select min sequence/arrival dttm bed number changes previous beds in sequence same first

update: actual solution based on answers provided

with cte_bed_moves (  select  movements.[facility (location)] ,movements.[person id] ,movements.[visit id] ,movements.[room (tracking location)] ,movements.[location sequence number]  ,movements.[arrival location dt/tm] arrival_dttm ,min_next_bed.arrival_dttm end_dttm  edcs_firstnet_bed_movements movements outer apply  (/*find next bed not same type current*/ select min(apply_nextbed.[arrival location dt/tm]) arrival_dttm edcs_firstnet_bed_movements apply_nextbed     movements.[facility (location)] = apply_nextbed.[facility (location)]     , movements.[person id] = apply_nextbed.[person id]     , movements.[visit id] = apply_nextbed.[visit id]     , apply_nextbed.[location sequence number] > movements.[location       sequence number]     , apply_nextbed.[room (tracking location)] <> movements.[room (tracking location)]  ) min_next_bed )  /*for each bed, rid of duplicates times inbetween*/ select   [facility (location)] facility_name ,[person id] person_id ,[visit id] ed_visit_id ,[room (tracking location)] room ,end_dttm ,min(arrival_dttm) arrival_dttm cte_bed_moves group [facility (location)] ,[person id] ,[visit id] ,[room (tracking location)] ,end_dttm 

assuming table named person_dttm following should work:

select arr.personsk, min(arr.arrivaldttm) startdttm, (select min(p2.arrivaldttm)  person_dttm p2   p2.personsk=arr.personsk , p2.arrivaldttm > arr.arrivaldttm , p2.room <> arr.room) enddttm person_dttm arr group arr.personsk, arr.room order arr.personsk, arr.arrivaldttm 

the basic idea select list people , earliest arrival in each room. add subquery selects minimum arrival time of records of same patient, excluding rows same room , excluding rows happened earlier.