SQL Server:按顺序删除同一房间的冗余行

Question

有跟踪一个人在床上的运动的数据，例如

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

所需输出为

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

想不出办法做到这一点

，我想强加的逻辑如下：

• 选择最小顺序/到达 dttm，其中床号发生变化，但所有先前的床顺序都与第一个相同

更新：我根据提供的
答案的实际解决方案

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 (Location)] as facility_name
,[Person Id] as person_id
,[Visit Id] as ed_visit_id
,[Room (Tracking Location)] as room
,end_dttm
,min(arrival_dttm) as arrival_dttm
from cte_bed_moves
group by
[Facility (Location)]
,[Person Id]
,[Visit Id]
,[Room (Tracking Location)]
,end_dttm

Answer 1

假设您的表被命名为 person_dttm 以下应该可以工作：

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

基本思想是选择列出人员及其最早到达每个房间的人。然后添加一个子查询，该子查询选择同一患者的所有记录的最短到达时间，不包括同一房间的行和之前发生的行。

Answer 2

使用相邻行在自身上连接表：

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

之后，您将不得不计算新的序列号，最简单的方法是再次加入子查询：

... 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
...

Answer 3

这是我使用分析函数的两分钱：

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

由于Sequence是一个保留字，因此您需要用双引号将其括起来。"Departure dttm"也是如此，因为您不能有带空格的别名。

在这里，它正在处理SQLFiddle：http://sqlfiddle.com/#!15/4940a/1

请注意，我之所以使用 postgresql，是因为 sqlserver 不稳定。辛塔塞是一样的。