ORACLE SQL Query only Overlapping Timeframes

Question

Data From Emp table:

NUM  DEPT   HIRED_SDT                       HIRED_EDT
---  -----  ------------------------------  ------------------------------
1    Sales  October, 01 2012 11:00:00+0000  October, 01 2012 11:30:00+0000
2    Sales  October, 01 2012 11:22:00+0000  October, 01 2012 12:40:00+0000
3    Sales  October, 01 2012 13:00:00+0000  October, 01 2012 13:50:00+0000
4    CC     October, 01 2012 14:00:00+0000  October, 01 2012 15:00:00+0000
5    CC     October, 01 2012 15:00:00+0000  October, 01 2012 16:10:00+0000
6    CC     October, 01 2012 16:00:00+0000  October, 01 2012 17:20:00+0000

Desired Result:

Only the overlapping timeframes grouped per departement

==> eg. within Sales there are 2 out of 3 records overlapping

NUM  DEPT   HIRED_SDT                       HIRED_EDT
---  -----  ------------------------------  ------------------------------
1    Sales  October, 01 2012 11:00:00+0000  October, 01 2012 11:30:00+0000
2    Sales  October, 01 2012 11:22:00+0000  October, 01 2012 12:40:00+0000
5    CC     October, 01 2012 15:00:00+0000  October, 01 2012 16:10:00+0000
6    CC     October, 01 2012 16:00:00+0000  October, 01 2012 17:20:00+0000

http://sqlfiddle.com/#!4/91730/1

Answer 1

You could try using EXISTS for this:

SELECT *
FROM Emp e
WHERE EXISTS (
  SELECT *
  FROM Emp
  WHERE DEPT = e.DEPT
    AND HIRED_SDT < e.HIRED_EDT
    AND HIRED_EDT > e.HIRED_sDT
    AND NUM <> e.NUM
);

I.e. select a row if another row exists that belongs to the same department and has a range overlapping with this row's one.