Home » SQL & PL/SQL » SQL & PL/SQL » Overlapping intervals
Overlapping intervals [message #208587] Mon, 11 December 2006 07:40 Go to next message
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.
Re: Overlapping intervals [message #208666 is a reply to message #208587] Mon, 11 December 2006 19:52 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Overlapping intervals [message #208743 is a reply to message #208666] Tue, 12 December 2006 01:26 Go to previous message
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

G.
Previous Topic: Changing Username
Next Topic: SELECT statement with subtotal and total & Rollup
Goto Forum:
  


Current Time: Fri Dec 09 19:20:31 CST 2016

Total time taken to generate the page: 0.11885 seconds