Subtracting between rows

Question

I have the below code written to subtract from different rows in SQL (but only the ones that have the same Room and Date), to get the difference in time between rows. It works, but it misses a few needed scenarios.

I only want to subtract the most recent dates from each other. Below, you see the beginning of my results.

     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

I'm trying to calculate the time the room is unused between every patient that finishes and every new patient that comes in, but in this case I'm getting every time difference between patients on the same day (ie. I do not want the difference between Patient A and Patient C)

I also would like to be able to add case statements so that if the difference is greater than a certain amount (say 90 minutes), it is not counted.

Please advise.

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

You need to rank your Room / Date so that you can get the detail from the next record. Something like this:

 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