CODEWITHSUNDEEP
sqlsql-serversql-server-2012
DDiVita
DDiVita

Reputation: 4265

Determine overlapping times between record sets

I have 2 sets of data I need to compare via time stamps. I need to determine how long a staff member is with a patient in a specific room. Initially, we were told the patient arrives to a room first and is the last to leave. This is not the case looking at our data now. This is the query we were using:

Select... 
Inner Join Staff on Staff.LocationId = Patient.LocationId 
Where Staff.StartTime >= Patient.StartTime and Staff.EndTime <= Patient.EndTime

While this query works for those scenarios, there are times where the staff is already in the room and may leave later or earlier. What we want to gather is the time at which both people are in the room to the time they are no longer together.

Given the example below, the staff member is already in the room before the patient. The staff member leaves before the Patient has left.

PatientId LocationId LocationName StartTimeInRoom         EndTimeInRoom
========= ========== ============ ===============         =============
7109      19         Testing      2015-05-19 10:02:11.483 2015-05-19 10:36:24.617

UserId LocationId LocationName StartTimeInRoom         EndTimeInRoom
====== ========== ============ ===============         =============
27     19         Testing      2015-05-19 10:00:11.900 2015-05-19 10:03:41.547

The desired outcome would look like this:

PatientId UserId LocationId LocationName TimeWithPatient StartTimeInRoom         EndTimeInRoom
========= ====== ========== ============ =============== ===============         =============
7109      27     19         Testing      90              2015-05-19 10:02:11.483 2015-05-19 10:03:41.547

So the above scenario is when the Staff member is already in the room. So, we can use the time at which the patient enters the room until the staff member leaves.

The problem I am having is figuring out if there is an overlap and if one exists between a patient and a staff member then determine which dates to use for their visit.

I have tried creating separate queries for each scenario, but it did not help me to determine the overlaps.

Update: I wonder if this would work:

Select...
Inner Join Staff on Staff.LocationId = Patient.LocationId 
where (Staff.StartTime BETWEEN Patient.StartTime and Patient.EndTime) 
      or (Patient.StartTime between Staff.StartTime and Staff.EndTime)

Upvotes: 0

Views: 70

Answers (2)

russ
russ

Reputation: 624

with cte (startime,endtime)
as
(
select
(select top 1 startime from staff where staffid = 27 and locationid = 19
and startime > '2015-05-18' order by startime),
(select top 1 endtime from staff where staffid = 27 and locationid = 19 and
startime > '2015-05-18' order by endtime desc)
)
select * from cte

Let me know if I am heading in the right direction, what this code does is shows the first time the staff entered the room on the day (let's say we are querying the database on 19/05/2015) and the last time they left...using the inner join it could be modified to show the first and last time the staff entered the room whilst the patient was in there. I realise it might have to work the other way round (ie staff entered first) and the various combinations, but these could all be incorporated into modified versions of this. Of course we are dealing only with the time from when the second individual to enter the room, entered the room, to the time when the first individual to leave the room, left the room, there would be no difficulty with adding up only the times they were in the room together if you wish...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can use inequalities in the join condition to get overlaps. Then use case to get the earliest and latest time:

Select (case when p.StartTime > s.StartTime then p.StartTime else s.StartTime
        end) as StartTime,
       (case when p.EndTime < s.EndTime then p.EndTime else s.EndTime
        end) as EndTime
from Patient p Inner Join
     Staff s
     on s.LocationId = p.LocationId qne
        s.StartTime <= p.EndTime and s.EndTime >= p.StartTime;

Note: If a patient and/or staff enters a room multiple times without the other leaving, then this will produce the longest time from the first time both were together to the last time. If this is an issue, then ask another question with sample data and desired results.

Upvotes: 4

Related Questions