finding time concurrency with an oracle select

Question

I have the following data in a DB table and would like to find concurrent transactions based on the start and end time.

Patient          Start Time        End Time
John             08:31A            10:49A 
Jim              10:14A            10:30A
Jerry            10:15A            10:28A
Alice            10:18A            12:29P
Bobby            10:32A            10:49A
Sally            10:46A            10:55A
Jane             10:52A            11:29A
Jules            10:54A            11:40A
Adam             10:58A            11:25A
Ben              11:00A            11:20A
Ann              11:31A            11:56A
Chris            11:49A            11:57A
Nick             12:00P            12:21P
Dave             12:00P            12:35P
Steve            12:23P            12:29P

If I want to find any overlapping times with a particular input, how would I write it? For example say I want to find overlaps with the 10:58A-11:25A time. This needs to find potential time concurrencies. I am looking for a count of the maximum number of concurrent overlaps. In my example for 10:58A-11:25A, I would want to see the following (count would be 5):

Patient          Start Time        End Time
Alice            10:18A            12:29P
Jane             10:52A            11:29A
Jules            10:54A            11:40A
Adam             10:58A            11:25A
Ben              11:00A            11:20A

In my second example some of the concurrent times overlap with the time I am looking for, but they are over before another range start. So, say I am looking for 10:46A-10:55A. I would expect a count of 4. In this example 08:31A-10:49A is dropped because it is over before 10:52A-11:29A starts. Same with 10:32A-10:49A, it was over before 10:52A-11:29A started. So, the most concurrent with the 10:46A-10:55A range would be 4, even though overall there is 6 (2 dropped).

Patient          Start Time        End Time
Alice            10:18A            12:29P
Sally            10:46A            10:55A
Jane             10:52A            11:29A
Jules            10:54A            11:40A

Can this be done with a sql statement?

Answer 1

The most easy and clean method is to exclude rows:

All rows except:

• rows than ends before start time
• rows than begins after end time

sample:

select * from your table t
where
   not ( 
     t.end < begin_time 
     or
     t.start > end_time 
   )

Sample in sqlfiddle