-1

I need to write some SQL to find overlapping date ranges that take place in the same room. For example, I have fields room_num, start_time, and stop_time. Each record represents a session taking place in that room_num. I need to find if any sessions that have the same room number will overlap each other.

Lance Roberts
  • 22,383
  • 32
  • 112
  • 130
user2687616
  • 1
  • [Please see this link](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap). It should point you in the right direction. In the future, please include some sample data/show that you have done some research before posting a question. – Mike Abramczyk Aug 15 '13 at 23:01
  • 1
    This question cannot be answered in its current state – Gayot Fow Aug 15 '13 at 23:16

1 Answers1

0

You can try something like this. Please note - I haven't tested it! 

select e1.room_num, 
   e1.start_time, e1.stop_time
   e2.start_time, e2.stop_time 
from events e1, events e2
where 
   (e1.start_time between e2.start_time and e2.end_time
    or e1.end_time between e2.start_time and e2.end_time)
   and e1.room_num = e2.room_num

If between doesn't work (could be Oracle specific), rewrite it as two separate >= and <= conditions.

Hank
  • 121
  • 1
  • 8