I want an Oracle query for getting time ranges

Question

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   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

Welcome to StackOverflow! To the right when you were asking your question there was this handy **How to Format** box. Worth a read, as is [the page linked](http://stackoverflow.com/editing-help) from the **[?]** just above the question area. I've done some formatting for you, but your question is still unclear (in particular there seems to be an incomplete sentence at the end). The more clear your question, the better both the quality and quantity of answers you'll get. Best, — T.J. Crowder, Apr 28 '11 at 07:31
Could you please clarify what exactly do you want to get in your output? — andr, Apr 28 '11 at 08:13
Would you also expect to see 5:10:45PM to 5:12:37PM and 6:08:23PM to 6:20:47PM? — Mark Baker, Apr 28 '11 at 08:27
You can probably build a solution with the method described in another SO question: [ORACLE SQL Date range intersections](http://stackoverflow.com/q/3476733/119634) — Vincent Malgrat, Apr 28 '11 at 08:27

score 2 · Answer 1 · answered Apr 28 '11 at 17:51

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;

I want an Oracle query for getting time ranges

1 Answers1