title: Combine query results into a single row and display time span?
body: <p>First, I apologize for not having a fiddle, but my data set was too large. So I am <a href="https://onedrive.live.com/?cid=CCBE197DAD50B153&amp;id=ccbe197dad50b153%21134" rel="nofollow noreferrer">including a file</a>. </p>
  
  <p>I have a set of queries that combine user data with location data. The location history data is gathered from RF devices and a history for that user is persisted. Going against the supplied <a href="https://onedrive.live.com/?cid=CCBE197DAD50B153&amp;id=ccbe197dad50b153%21134" rel="nofollow noreferrer">SQL file</a> I need to combine the data where a Staff member may enter a room, leave that room, then come back. This example may constitute a visit with a patient. Another example is if a Staff member is logged in a room consecutively. We are only concerned with data where the time stamps are greater than 2 minutes in length. Meaning the RF reader may read a tag if a staff member walks by a room and that location is logged. Here are the intial set of queries:</p>
  
  <pre><code>with 
  
  StaffHistory as(
  SELECT
  LocationHistories.UserProfileId,
  UserProfiles.FirstName,
  UserProfiles.LastName,
  LocationHistories.LocationId,
  Locations.LocationName,
  LocationHistories.LocationHistoryTimeStamp,
  PreviousLocationTime = LAG(LocationHistories.LocationHistoryTimeStamp, 1) OVER 
    (PARTITION BY LocationHistories.UserProfileId ORDER BY LocationHistories.LocationHistoryTimeStamp),
  NextLocationTime = Lead(LocationHistories.LocationHistoryTimeStamp, 1) OVER 
    (PARTITION BY LocationHistories.UserProfileId ORDER BY LocationHistories.LocationHistoryTimeStamp)
  FROM
  LocationHistories
  INNER JOIN UserProfiles ON LocationHistories.UserProfileId = UserProfiles.Id
  INNER JOIN Locations ON LocationHistories.LocationId = Locations.Id
  where LocationTrackingType = 1),
  
  StaffInRoomTime as(
  SELECT
  StaffHistory.UserProfileId,
  StaffHistory.FirstName,
  StaffHistory.LastName,
  StaffHistory.LocationId,
  StaffHistory.LocationName,
  DATEDIFF(SECOND, LocationHistoryTimeStamp, NextLocationTime) as TimeSpentInRoom,
  StaffHistory.LocationHistoryTimeStamp,
  StaffHistory.PreviousLocationTime,
  StaffHistory.NextLocationTime
  
  FROM
  StaffHistory
  Where DATEDIFF(SECOND, LocationHistoryTimeStamp, NextLocationTime) &gt; 120
  )
  
  select * from StaffInRoomTime ORDER BY UserProfileId, LocationHistoryTimeStamp
  </code></pre>
  
  <p>I used common table expressions just for this example. These are actual views in the DB. </p>
  
  <p>The first query joins the histories with the staff. We also create a couple columns for the Previous logged time and the Next logged time. This is so we can determine the length of time in a room. </p>
  
  <p>The second query pulls from the first query and we set a column for how long the staff member was in that location during that time and we also filter any data out where the <code>LocationHistoryTimeStamp</code> and <code>NextLocationTime</code> are greater than 2 minutes.</p>
  
  <p>What I am trying to achieve is combine the data where a staff member may be logged for a room consecutively or if they leave the room and come back. </p>
  
  <p>Here is an example of the data set where the Staff member is in a room consecutively for:
  <img src="https://i.stack.imgur.com/tTyI0.png" alt="enter image description here"></p>
  
  <p>Here would be the outcome:
  <img src="https://i.stack.imgur.com/e2UJH.png" alt="enter image description here"></p>
  
  <p>Here is an example of spanning multiple rooms for a given visit:
  <img src="https://i.stack.imgur.com/qWYpf.png" alt="enter image description here"></p>
  
  <p>I have tried using a <code>Inner Join</code> on the second query that uses the <code>Min(LocationHistoryTimeStamp)</code>. However, the time spans ended up not being correct so I am missing something.</p>
  
  <p>This was the Inner Join query I tried:</p>
  
  <pre><code> Select
      StaffHistory.LocationId, 
      StaffHistory.LocationName, 
      StaffHistory.UserProfileId,
      Min(LocationHistoryTimeStamp) as LocationHistoryTimeStamp,
  
      DATEDIFF(SECOND, Min(LocationHistoryTimeStamp), Lead(Min(LocationHistoryTimeStamp), 1) OVER 
        (PARTITION BY UserProfileId ORDER BY Min(LocationHistoryTimeStamp))) As TimeSpentInRoom,
  
      NextLocationTime = Lead(Min(LocationHistoryTimeStamp), 1) OVER 
        (PARTITION BY UserProfileId ORDER BY Min(LocationHistoryTimeStamp))
      FROM
      StaffHistory 
      Where TimeSpentInRoom &gt; 120
      GROUP BY LocationId,LocationName,UserProfileId
  </code></pre>