sql - 行间相减

Question

我编写了以下代码以从 SQL 中的不同行(但仅限具有相同房间和日期的行)中减去，以获得行之间的时间差。它有效，但它遗漏了一些需要的场景。

我只想相互减去最近的日期。在下面，您会看到我的结果的开头。

     PatientName PatientName  OutTime   InTime  Room    Room    Value
     Patient A   Patient B     45:00.0  55:00.0 OR 1    OR 1    10
     Patient A   Patient C     45:00.0  55:00.0 OR 1    OR 1    130
     Patient B   Patient C     00:00.0  55:00.0 OR 1    OR 1    55

我正在尝试计算每个完成治疗的患者和每个进来的新患者之间房间未使用的时间，但在这种情况下，我得到同一天患者之间的每个时间差(即我做不想要患者 A 和患者 C 之间的区别)

我还希望能够添加 case 语句，以便如果差异大于一定数量(比如 90 分钟)，则不计算在内。

请指教。

        --This code creates the table with Dummy Data

        CREATE TABLE #OperatingRoom (
        [Date] date,
        [Room] varchar(255),
    [PatientName] varchar(255),
        [InTime] time,
        [OutTime] time,
        ); 

        INSERT INTO #OperatingRoom ([Date],[Room],[PatientName],[InTime],        [OutTime])
        VALUES ('01-01-2019', 'OR 1', 'Patient A', '08:02:00','09:45:00'),
        ('01-01-2019', 'OR 1', 'Patient B', '09:55:00','11:00:00'),
        ('01-01-2019', 'OR 1', 'Patient C', '11:55:00','14:00:00'),
        ('01-02-2019', 'OR 1', 'Patient D', '08:59:00','09:14:00'),
        ('01-02-2019', 'OR 1', 'Patient E', '11:02:00','13:30:00'),
        ('01-02-2019', 'OR 2', 'Patient F', '14:02:00','16:02:00'),
        ('01-03-2019', 'OR 2', 'Patient B', '07:55:00','11:00:00'),
        ('01-03-2019', 'OR 2', 'Patient C', '11:55:00','13:00:00'),
        ('01-03-2019', 'OR 3', 'Patient D', '08:59:00','09:14:00'),
        ('01-03-2019', 'OR 2', 'Patient E', '13:02:00','13:30:00'),
        ('01-03-2019', 'OR 3', 'Patient F', '14:02:00','16:02:00')
        ;

        --This code performs the object of the query 

        SELECT 
        T1.PatientName,
        T2.PatientName,
        T1.[OutTime]
       ,T2.InTime,
        T1.Room,
        T2.Room,
        datediff(mi,T1.OutTime,T2.InTime) AS Value
        FROM #operatingroom T1
        JOIN #operatingroom T2
        ON T2.[OutTime] > T1.[InTime]
        and T1.[Date] = T2.[Date] 
        and T1.Room = T2.Room 
        and T1.PatientName <> T2.PatientName

Answer 1

您需要对您的房间/日期进行排名，以便您可以从下一条记录中获取详细信息。像这样:

 WITH Myrooms as (
      Select  Date, 
              Room, 
              PatientName, 
              InTime,
              OutTime, 
              DENSE_RANK() OVER (PARTITION BY Room, Date ORDER BY InTime) as PatientRank 
      From #OperatingRoom )

  Select Date, 
         Room, 
         PatientName, 
         M.InTime, 
         OutTime,
         CASE WHEN DateDiff(mi,M.OutTime, aa.InTime) >90 then NULL 
         else DateDiff(mi,M.OutTime, aa.InTime) END as Value 
  from Myrooms M
  OUTER APPLY
  (Select InTime from MyRooms MM 
   WHERE MM.Room=M.Room and MM.Date=M.Date and MM.PatientRank=M.PatientRank+1) aa