I want an Oracle query for getting time ranges

My FIRST table TIMINGS is like

EMPCODE,Att_DATE,IN_TIME,OUT_TIME

My MAIN table is

EMPCODE,DESIGANTION,LACATION ETC.

The data in my TIMINGS table is like the following:

10101  7/2/2010   7/2/2011 7:08:31 AM     7/2/2011 12:05:31 PM
10101  7/2/2010   7/2/2011 12:58:07 PM    7/2/2011 1:47:36 PM
10101  7/2/2010   7/2/2011 2:17:52 PM     7/2/2011 4:37:23 PM

10102  7/2/2010   7/2/2011 9:44:07 AM     7/2/2011 10:17:53 AM
10102  7/2/2010   7/2/2011 10:25:21 AM    7/2/2011 11:18:28 AM
10102  7/2/2010   7/2/2011 11:28:32 AM    7/2/2011 12:10:31 PM
10102  7/2/2010   7/2/2011 12:35:15 PM     7/2/2011 04:10:04 PM

10103  7/2/2010   7/2/2011 8:56:52 AM     7/2/2011 10:03:39 AM
10103  7/2/2010   7/2/2011 10:10:02 AM    7/2/2011 12:00:07 PM
10103  7/2/2010   7/2/2011 12:22:54 PM    7/2/2011 4:11:03 PM
10103  7/2/2011开发者_如何学JAVA   7/2/2011 4:51:45 PM     7/2/2011 5:10:45 PM

10104  7/2/2010   7/2/2011 5:12:37 PM     7/2/2011 6:08:23 PM
10104  7/2/2010   7/2/2011 6:20:47 PM     7/3/2011 5:04:33 AM

then i want to display output as

NO one present in ofc in the following timing pairs

from-time    to-time
00:00:00     7:08 AM
12:10:31PM   12:22:54PM
4:37:23PM    4:51:45PM

Queries like this one are best built inside out.

First you need a result set which just lists times and changes of people.

  SELECT to_date('2010/07/02', 'yyyy/mm/dd') as event_time
    , 0 as change
  FROM dual
UNION ALL
  SELECT IN_TIME as event_time
    , 1 as change
  FROM timings
  WHERE att_date = to_date('2010/07/02', 'yyyy/mm/dd')
UNION ALL
  SELECT OUT_TIME as event_time
    , -1 as change
  FROM timings
  WHERE att_date = to_date('2010/07/02', 'yyyy/mm/dd')

Next you need intervals and a running total. Oracle has a mechanism for this, see http://www.orafaq.com/node/55 for details.

SELECT event_time as interval_start
  , lead(event_time, 1, to_date('2010/07/03', 'yyyy/mm/dd')
    OVER (ORDER BY event_time) as interval_end
  , sum(change) OVER (ORDER BY event_time)
    ROWS BETWEEN (UNBOUNDED PRECEDING AND CURRENT ROW) as people
FROM (
    previous query here
  )

This will give you people and intervals. You now just need to filter things out.

SELECT interval_start, interval_end
FROM (
    previous query here
  )
WHERE people = 0;