我想要一个 Oracle 查询来获取时间范围

Question

我的第一个表 TIMINGS 就像

EMPCODE,Att_DATE,IN_TIME,OUT_TIME

我的 MAIN 表是

EMPCODE,DESIGANTION,LACATION ETC.

我的 TIMINGS 表中的数据如下所示：

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

然后我想将输出显示为

NO OFC 中存在以下时序对中的一个

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

Answer 1

像这样的查询最好是由内而外构建的。

首先，您需要一个仅列出时间和人员变化的结果集。

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

接下来您需要间隔和运行总计。 Oracle对此有一个机制，请参阅http://www.orafaq.com/node/55了解详情。

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
  )

这将为您提供人员和间隔。您现在只需要过滤掉一些东西。

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