OraFAQ Forum: SQL & PL/SQL » Overlapping intervals

Home » SQL & PL/SQL » SQL & PL/SQL » Overlapping intervals

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Overlapping intervals [message #208587]

Mon, 11 December 2006 07:40

gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy

Member

Hi all!
I have the following scenario.
A table OPERATOR_LIST containing a list of operators ID's with their "desk" ID and working hours.

DROP TABLE OPERATOR_LIST
/
CREATE TABLE OPERATOR_LIST (
  OPERATOR_ID NUMBER(3)   PRIMARY KEY,
  DESK_ID     VARCHAR2(3) NOT NULL,
  START_TIME  DATE        NOT NULL,
  END_TIME    DATE        NOT NULL
)
/
INSERT INTO OPERATOR_LIST VALUES (
1, 'PC1', TO_DATE('30/12/1899 08.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 12.00.00', 'DD/MM/YYYY HH24.MI.SS'))
/
INSERT INTO OPERATOR_LIST VALUES (
2, 'PC2', TO_DATE('30/12/1899 08.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 12.00.00', 'DD/MM/YYYY HH24.MI.SS'))
/
INSERT INTO OPERATOR_LIST VALUES (
3, 'PC2', TO_DATE('30/12/1899 09.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 12.30.00', 'DD/MM/YYYY HH24.MI.SS'))
/
INSERT INTO OPERATOR_LIST VALUES (
4, 'PC3', TO_DATE('30/12/1899 08.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 12.00.00', 'DD/MM/YYYY HH24.MI.SS'))
/
INSERT INTO OPERATOR_LIST VALUES (
5, 'NB1', TO_DATE('30/12/1899 08.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 12.00.00', 'DD/MM/YYYY HH24.MI.SS'))
/
INSERT INTO OPERATOR_LIST VALUES (
6, 'NB1', TO_DATE('30/12/1899 13.00.00', 'DD/MM/YYYY HH24.MI.SS'), TO_DATE('30/12/1899 17.00.00', 'DD/MM/YYYY HH24.MI.SS'))
/

The requirement is that at a given time, there must be only a single operator on a certain "desk".
And thus in the previous example the records with OPERATOR_ID = 2 and OPERATOR_ID = 3 are incompatible (they are overlapping).
How can I find all the incompatible records in table OPERATOR_LIST?

Thanks a lot,
G.

Report message to a moderator

Re: Overlapping intervals [message #208666 is a reply to message #208587]

Mon, 11 December 2006 19:52

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Something like this?

SELECT *
FROM OPERATOR_LIST o
WHERE EXISTS (
    SELECT 1
    FROM   OPERATOR_LIST
    WHERE  OPERATOR_ID = o.OPERATOR_ID
    AND    START_TIME  < o.END_TIME
    AND    END_TIME    > o.START_TIME
    AND    ROWID      <> o.ROWID
)

An index on (OPERATOR_ID, START_TIME, END_TIME) will help this along, but not indefinitely. If there are are thousands of entries for each operator, and thousands of operators, then this SQL might not scale very well. You could add hints to try to encourage a MERGE SEMI JOIN, which should make it scalable (but not exactly super fast).

Ross Leishman

Report message to a moderator

Re: Overlapping intervals [message #208743 is a reply to message #208666]

Tue, 12 December 2006 01:26

gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy

Member

Thank you very much!
By the way:

rleishman wrote on Tue, 12 December 2006 02:52

If there are are thousands of entries for each operator

There will not be more than one entry for each operator, because OPERATOR_ID is primary key; on the other hand, there will be hundreds of operators.

Wink

Report message to a moderator

Previous Topic:	Changing Username
Next Topic:	SELECT statement with subtotal and total & Rollup

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 12 21:22:57 CST 2025