时间戳

首页 > 解决方案 > Oracle中的超时和超时考勤SQLQuery

sql - Oracle中的超时和超时考勤SQLQuery

问题描述

我在 oracle 中有一个表格，其中包含以下示例输出。

EID   | type  |   Date
24    |  IN   |03/25/2019 6:45 am
24    |  OUT  |03/25/2019 8:05 am
24    |  IN   |03/25/2019 8:06 am
24    |  IN   |03/25/2019 8:28 am
24    |  OUT  |03/25/2019 9:48 am
24    |  IN   |03/25/2019 9:52 am
24    |  IN   |03/25/2019 9:57 am
24    |  IN   |03/25/2019 10:44 am
24    |  OUT  |03/25/2019 12:16 pm
24    |  OUT  |03/25/2019 1:00 pm
24    |  IN   |03/25/2019 1:05 pm
24    |  OUT  |03/25/2019 2:21  pm

我想构建一个查询来实现以下结果：

EID |       TIMEIN        |    TIMEOUT            | DIIF_IN_MIN 
24  | 03/25/2019 6:45 am  | 03/25/2019 8:05 am    |   1 
24  | 03/25/2019 8:06 am  |       null            |   0
24  | 03/25/2019 8:28 am  | 03/25/2019 9:48 am    |   4
24  | 03/25/2019 9:52 am  |       null            |   0
24  | 03/25/2019 9:57 am  |       null            |   0
24  | 03/25/2019 10:44 am | 03/25/2019 12:16 pm   |   0
24  |        null         | 03/25/2019 1:00 pm    |   5
24  | 03/25/2019 1:05 pm  | 03/25/2019 2:21  pm   |   0

标签： sqloracleoracle11gsql-query-store

解决方案

您可以通过引导窗口分析函数的贡献来使用这样的逻辑

with tab(eid, type, dates ) as
(
 select 24,'IN' ,timestamp'2019-03-25 06:45:00' from dual union all
 select 24,'OUT',timestamp'2019-03-25 08:05:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 08:06:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 08:28:00' from dual union all
 select 24,'OUT',timestamp'2019-03-25 09:48:00' from dual union all
 select 24,'IN' ,timestamp'2019-03-25 09:52:00' from dual    
)
select t1.eid, t1.dates as timein, t2.dates as timeout, 
       nvl(to_number(regexp_substr(to_char(t1.ld_dates - t2.dates),'[^:]+',1,2)),0) 
           as diff_in_minutes
  from ( select lead(dates) over (order by dates) as ld_dates, t.* 
           from tab t 
          where type = 'IN' order by dates) t1
  full join ( select * from tab where type = 'OUT' order by dates) t2
    on t1.dates <= t2.dates and ld_dates > t2.dates
 order by t1.dates;

EID TIMEIN              TIMEOUT             DIFF_IN_MINUTES
24  25.03.2019 06:45:00 25.03.2019 08:05:00 1
24  25.03.2019 08:06:00 NULL                0
24  25.03.2019 08:28:00 25.03.2019 09:48:00 4
24  25.03.2019 09:52:00 NULL                0

Demo

推荐阅读