0

Say I got a meeting room named niagara. I want to find who occupied this room given a start and end time range. The table name is "niagara". Lets just keep the search for today.

Person InTime      OutTime
A       9AM         1PM
B       10AM        12PM
C       10:25AM     1:30PM
D       9AM         9:00 PM
E       12:20PM     5PM
F       10:45 AM    11:30 PM

Give the list of persons who occupied between 10:30 AM and 12:15 PM

Expected Answer is - A,BC,D and F

How to do this

I tried 

SELECT PERSON 
FROM NIAGARA 
WHERE (IN_TIME > START_TIME AND OUT_TIME < END_TIME) 
   OR (IN_TIME < START_TIME AND OUT_TIME > END_TIME)

BTW I was asked this in a job interview.

So which means this is the way I am trying to learn the answer

juniorbansal
  • 1,249
  • 8
  • 31
  • 51
  • 4
    What are data types for `InTime` and `OutTime`? – PM 77-1 Feb 04 '16 at 18:34
  • 2
    What have you tried so far? Can you show us your SQL statement? The purpose of StackOverflow is not to write the statement for you, but to help if it's not working. – BryanT Feb 04 '16 at 18:36
  • Is the concatenation you've used, `||`, supposed to be `OR`? What happened when you tried this? – Alex Poole Feb 04 '16 at 18:39
  • the answer they probably wanted was to change the columns to a datatype of DateTime, then it would be easy to to use standard functions when working with the table. – Dave Kelly Feb 04 '16 at 18:53
  • 1
    Here is the answer http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap – Dave Kelly Feb 04 '16 at 19:03

3 Answers3

2

The basic logic is that someone is in the room if the in_time is less than the period end and the out_time is after the period start. So, that would be:

SELECT PERSON
FROM NIAGARA
WHERE OUT_TIME > START_TIME AND IN_TIME < END_TIME;

How you actually express this in Oracle depends on how the values are stored. As phrased, it seems like they are stored as strings. Doing the actual comparisons would then require more work, but the same logic holds.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

The common logic to check for overlapping ranges is this:

(start#1,end#2) overlaps (start#2,end#2)

start#1 <= end#2 AND end#1>= start#2

Depending on your logic (both start & end inclusive or only one) you might need to change the comparison to 

start#1 < end#2 AND end#1>= start#2
dnoeth
  • 59,503
  • 4
  • 39
  • 56
0

try this:

SELECT PERSON 
FROM NIAGARA 
WHERE IN_TIME BETWEEN  '10:30AM' and '12:15PM' AND OUT_TIME BETWEEN  '10:30AM' and '12:15PM'

Shiv looking at your question description you gave you should not return any results as none of the rows satisfy your condition.

The only inTime that satisfies the condition InTime BETWEEN '10:30AM' and '12:15PM' is F but the OutTime does not satisfy the condition OutTime BETWEEN '10:30AM' and '12:15PM'

The only OutTime that satisfies the condition OutTime BETWEEN '10:30AM' and '12:15PM' is B but the inTime does not satisfy the condition InTime BETWEEN '10:30AM' and '12:15PM'

Fuzzy
  • 3,810
  • 2
  • 15
  • 33