Home Ask Login Register

Developers Planet

Your answer is one click away!

michaelBurns February 2016

SQL Server : remove redundant rows where same room in sequence

Have data that tracks a 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 a way to do this, the logic I want to impose is the following:

  • Select min sequence/arrival dttm where bed number changes but all previous beds in sequence were the same as the first

Update: My actual solution based on answers provided

WITH cte_bed_moves as (

 movements.[Facility (Location)]
,movements.[Person Id]
,movements.[Visit Id]
,movements.[Room (Tracking Location)]
,movements.[Location Sequence Number] 
,movements.[Arrival to Location Dt/Tm] as arrival_dttm
,min_next_bed.arrival_dttm as end_dttm
edcs_firstnet_bed_movements AS movements OUTER APPLY 
(/*Find next bed that is not the same type as the current*/
SELECT MIN(apply_nextBed.[Arrival to Location Dt/Tm]) as arrival_dttm
FROM edcs_firstnet_bed_movements AS apply_nextBed
    movements.[Facility (Location)] = apply_nextBed.[Facility (Location)]
    AND movements.[Person Id] = apply_nextBed.[Person Id]
    AND movements.[Visit Id] = apply_nextBed.[Visit Id]
    AND apply_nextBed.[Location Sequence Number] > movements.[Location       Sequence Number]
    AND apply_nextBed.[Room (Tracking Location)] <> movements.[Room (Tracking Location)]
 ) as min_next_bed

/*for each bed, get rid of the duplicates with times inbetween*/
 [Facility (Lo        


Bampfer February 2016

Assuming your table is named person_dttm the following should work:

SELECT arr.PersonSK, MIN(arr.ArrivalDttm) as StartDttm,
(SELECT MIN(p2.ArrivalDttm)
 FROM person_dttm p2 
 WHERE p2.PersonSK=arr.PersonSK AND p2.ArrivalDttm > arr.ArrivalDttm AND p2.Room <> arr.Room) as EndDttm
FROM person_dttm arr
GROUP BY arr.PersonSK, arr.Room
ORDER BY arr.PersonSK, arr.ArrivalDttm

The basic idea is to select the list people and their earliest arrival in each room. Then add a subquery which selects the minimum arrival time of all the records of that same patient, excluding rows for the same room and excluding rows that happened earlier.

Serge Seredenko February 2016

Join table on itself using neighbour rows:

SELECT T1.ArrivalDttm ArrivalDttm, T2.ArrivalDttm DepartureDttm, ...
  T1.PersonSK = T2.PersonSK AND T1.sequence + 1 = T2.sequence
  T1.Room != T2.Room

After that you will have to calculate new sequence number, the easiest way would be to join on subquery again:

... T3.sequence sequence
  SELECT COUNT(DISTINCT tmp.Room) AS sequence, tmp.PersonSK
  FROM T AS tmp
  WHERE tmp.ArrivalDttm <= T1.ArrivalDttm AND tmp.PersonSK = T1.PersonSK
) T3 ON T1.PersonSK = T3.PersonSK

Jorge Campos February 2016

Here is my two cents using Analytic Functions:

select b.PersonSK, 
       row_number() over (partition by b.PersonSk 
                           order by b2.ArrivalDttm) as "Sequence",
       lead(b2.ArrivalDttm) over (partition by b.PersonSk 
                                   order by b2.ArrivalDttm) as "Departure dttm"
  from beds b
        INNER JOIN 
         (SELECT PersonSK,
                 min(ArrivalDttm) ArrivalDttm
            FROM beds
           GROUP by PersonSK, room) b2 
        ON b.PersonSK = b2.PersonSK
           AND b.room = b2.room
           AND b.ArrivalDttm = b2.ArrivalDttm

As Sequence is a reserved word you need to wrap it with double quotes. The same with "Departure dttm" because you can't have an alias with a space.

Here it is working on SQLFiddle: http://sqlfiddle.com/#!15/4940a/1

Note that I made it with postgresql because sqlserver was unstable. The sintaxe is the same.

Post Status

Asked in February 2016
Viewed 3,006 times
Voted 9
Answered 3 times


Leave an answer

Quote of the day: live life