CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
Ingila Ejaz
Ingila Ejaz

Reputation: 399

Find Overlapping Times in Oracle

I am writing a query to detect overlapping times. It is for a room reservation system that should not make a reservation if the time slot user entered overlaps with any of the timeIn's and timeOut's stored already. I have my table structured as:

ID      BOARDROOM_TYPE  REQUEST_TIME            USER_REQUEST    SUBJECT                  FROM_DATE  TO_DATE     FROM_TIME               TO_TIME
17174   Board Room      06/11/2014 1:21:00 AM   User A          Some Subject             11/11/2014 11/11/2014  01/11/2014 2:30:00 PM   01/11/2014 5:00:00 PM
17172   Board Room      06/11/2014 12:50:58 AM  User B          Meeting                  11/11/2014 11/11/2014  01/11/2014 9:00:00 AM   01/11/2014 1:00:00 PM

Now according to this data, the room is reserved from 9 AM to 1 PM and then from 2:30 PM to 5 PM.

My Query for checking overlapping meeting times on the same date in the same room is:

select count(*) from meeting_data where not (to_time <= to_date('13:00','hh24:mi:ss') and from_time >= to_date('14:00','hh24:mi:ss')) and trunc(from_date) = trunc(Sysdate) and boardroom_type = 'Board Room' and deleted = 'N'

When timeIn = 13:00 and timeOut = 14:00, it returns 2 and if count > 0, then it should not insert data. It is working fine till this point.

The problem lies when I input overlapping times, like timeIn = 09:00 timeOut = 14:00, it still gives 2 and since count > 0 it inserts even though there's already a reservation made in this time slot (ID = 17172).

What am i doing wrong with my query?

Upvotes: 1

Views: 1058

Answers (3)

Srini
Srini

Reputation: 1

Why not use a cross join, it will get records which are in between the existing timeline.

SELECT COUNT (*) FROM MEETING_DATA A, MEETING_DATA B WHERE A.FROM_TIME > B.FROM_TIME AND A.FROM_TIME < B.TO_TIME AND ...;

Upvotes: 0

Multisync
Multisync

Reputation: 8797

select count(*) from meeting_data 
where NOT (
      to_date(to_char(to_time, 'YYYYMMDD') || '13:00','YYYYMMDDhh24:mi')
      not between from_time and to_time
  and to_date(to_char(to_time, 'YYYYMMDD') || '14:00','YYYYMMDDhh24:mi')
      not between from_time and to_time
  and to_time not between to_date(to_char(to_time, 'YYYYMMDD') || '13:00','YYYYMMDDhh24:mi')
                      and to_date(to_char(to_time, 'YYYYMMDD') || '14:00','YYYYMMDDhh24:mi')  
  )
  and from_date between trunc(Sysdate) and trunc(Sysdate+1) -1/24/60/60 
  and boardroom_type = 'Board Room' and deleted = 'N';

The first three conditions check if there is no intersections with any [from_date, to_date] + check that [from_date, to_date] is not inside the desired interval

Also there was a problem in your query:

The default date values are determined as follows:
- The year is the current year, as returned by SYSDATE.
- The month is the current month, as returned by SYSDATE.
- The day is 01 (the first day of the month).
- The hour, minute, and second are all 0.

So to_time <= to_date('13:00','hh24:mi:ss') could give you undesired results depending on what date you're performing this comparision.

And minor change with trunc(from_date) = trunc(Sysdate) I removed a function on the column (which is a good candidate to be indexed)

If the intervals [09:00 - 13:00] and [13:00 - 14:00] are considered as not intersected then you may use not strict comparision:

Change

some_date not between from_time and to_time

To

some_date <= from_time or some_date >= to_time

Here is the modified query:

select count(*) from meeting_data 
where (to_date(to_char(from_date, 'YYYYMMDD') || '13:00','YYYYMMDDhh24:mi') > from_date
       and to_date(to_char(from_date, 'YYYYMMDD') || '13:00','YYYYMMDDhh24:mi') < to_date
   or to_date(to_char(from_date, 'YYYYMMDD') || '14:00','YYYYMMDDhh24:mi') > from_date
      and to_date(to_char(to_time, 'YYYYMMDD') || '14:00','YYYYMMDDhh24:mi') < to_date
   or to_time > to_date(to_char(to_time, 'YYYYMMDD') || '13:00','YYYYMMDDhh24:mi')
      and to_time < to_date(to_char(to_time, 'YYYYMMDD') || '14:00','YYYYMMDDhh24:mi')
      )            
  and from_date between trunc(Sysdate) and trunc(Sysdate+1) -1/24/60/60 
  and boardroom_type = 'Board Room' and deleted = 'N';

Upvotes: 2

Chamal
Chamal

Reputation: 1449

As i understand your question you need to check the user entered values with the database. So i think stored procedure is the way to go here. You can pass user entered values to the SP and check time is overlapping.

CREATE OR REPLACE PROCEDURE check_date_clash (
  p_start_date IN DATE,
  p_end_date   IN DATE,
  l_count      OUT NUMBER
)
AS

BEGIN
  SELECT COUNT(*)
  INTO   l_count
  FROM   meeting_data
  WHERE  from_time <= p_end_date
  AND    to_time   >= p_start_date
  AND    trunc(from_date) = trunc(Sysdate) 
  AND    boardroom_type = 'Board Room'
  AND    deleted = 'N';

END check_date_clash;
/

This will get count of overlapping records with the user entered period and return the count.

Upvotes: 0

Related Questions