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 (

SELECT
 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
FROM 
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
WHERE
    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*/
select 
 [Facility (Lo        

Answers


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, ...
FROM T AS T1
LEFT JOIN T AS T2 ON
  T1.PersonSK = T2.PersonSK AND T1.sequence + 1 = T2.sequence
...
WHERE
  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
...
LEFT JOIN (
  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, 
       b2.ArrivalDttm,
       b.Room,
       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,
                 room, 
                 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

Search




Leave an answer