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)