Oracle 匹配范围内的行

我有3个表schedule_assignment，schedule_detail和access_history (下面的测试用例)

schedule_detail包含时间和位置的列表。

schedule_assignment将列表分配给员工。

access_history表记录员工刷卡时的信息。

我需要做的是匹配员工、位置和trunc日期行，然后浏览计划记录，并从access_history中找到相应的匹配HHMMSS

由于在日期上找到精确匹配的机会很小，因此我需要查看行的时间段。

通过这种方式，我可以确定员工是否准时，早，晚，超出时间范围或从未在该位置刷卡。


如果access_history时间为计划时间的-2分钟，则该行被认为是好的; 如果该行在计划时间前2分钟1秒或4分钟之间，则认为是提前; 如果该行在计划时间后2分钟1秒或4分钟之间，则认为是延迟的; 如果该行在计划时间之前或之后的4分钟1秒，则超出范围。

我在这个最初的查询中尝试了一下，以提供我试图做的事情的视觉效果。我知道它是错误的，因为时间表日期很少与access_history日期匹配。

SELECT sa.employee_id,  sd.location_id,
sd.schedule_date,
ah.access_date

FROM
schedule_assignment sa,
schedule_detail sd, 
access_history ah
WHERE 
sa.employee_id =
ah.employee_id AND
sd.location_id =
ah.location_id AND
(ah.access_date IS NULL OR 
TRUNC(sd.schedule_date) = TRUNC (ah.access_date)  
)
Order by schedule_date;

下面是详细的测试用例，其中包含预期的结果以及每行的详细说明。

任何有关如何查找范围内的行的指导都将不胜感激。

请注意，在下面的情况下，有一个没有access_history记录的时间表记录，因此也没有什么可以比较的。

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE table schedule_hdr(
  schedule_id NUMBER(4),
  schedule_name VARCHAR2(30)
);

INSERT INTO schedule_hdr(
  schedule_id,
  schedule_name
)
VALUES 
(5, 'Thursday Day Shift');


CREATE table schedule_assignment( 
   schedule_id NUMBER(4),
   employee_id NUMBER(6)
);

INSERT INTO schedule_assignment(
  schedule_id,
  employee_id   
)
VALUES (5,1);


create table schedule_detail(
       schedule_id NUMBER(4), 
       location_id number(4),
       schedule_date date
  );

insert into schedule_detail(
  schedule_id, 
  location_id,
  schedule_date)
 values 
  (5, 100,
TO_DATE('2020/07/23 11:00:00', 'yyyy/mm/dd hh24:mi:ss'));

insert into schedule_detail(
schedule_id, 
location_id,  schedule_date)
 values 
  (5, 101,
TO_DATE('2020/07/23 11:04:00', 'yyyy/mm/dd hh24:mi:ss'));

insert into schedule_detail(
schedule_id, 
location_id,  schedule_date)
 values 
  (5,102,
TO_DATE('2020/07/23 11:07:00', 'yyyy/mm/dd hh24:mi:ss'));

insert into schedule_detail(
schedule_id, 
location_id,  schedule_date)
 values 
  (5,103,
TO_DATE('2020/07/23 11:10:00', 'yyyy/mm/dd hh24:mi:ss'));


insert into schedule_detail(
schedule_id, 
location_id,  schedule_date)
 values 
  (5,100,
TO_DATE('2020/07/23 11:11:00', 'yyyy/mm/dd hh24:mi:ss'));

    create table access_history(
       employee_id NUMBER(6), 
       location_id number(4),
       access_date date
    );

insert into access_history(
employee_id, 
location_id,  access_date)
VALUES 
  (1, 100,
TO_DATE('2020/07/23 11:00:20', 'yyyy/mm/dd hh24:mi:ss'));


insert into access_history(
employee_id, 
location_id,  access_date)
VALUES 
  (1, 101,
TO_DATE('2020/07/23 11:01:53', 'yyyy/mm/dd hh24:mi:ss'));

insert into access_history(
employee_id, 
location_id,  access_date)
 values 
  (1, 102,
TO_DATE('2020/07/23 11:10:13', 'yyyy/mm/dd hh24:mi:ss'));


insert into access_history(
employee_id, 
location_id,  access_date)
 values 
  (1,103,
TO_DATE('2020/07/23 11:15:12', 'yyyy/mm/dd hh24:mi:ss'));


-- results of query should populate this table.

CREATE table schedule_history(

      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
         employee_id NUMBER(6), 
       schedule_id NUMBER(4),
       location_id number(4),
       schedule_date date,
      access_date date,
      status VARCHAR2(1)
);


SELECT * from schedule_history

SEQ_NUM    EMPLOYEE_ID    
SCHEDULE_ID LOCATION_ID    SCHEDULE_DATE    ACCESS_DATE    STATUS
1    1    5 100    07232020 11:00:00    07232020 11:00:20    G

-- This row GOOD because only 20 second difference from schedule 

2    1    5 101    07232020 11:04:00    07232020 11:01:53    E

-- This row early because because 2 minutes 7 seconds before schedule time

3    1    5 102    07232020 11:07:00    07232020 11:10:13    L

-- This row late because because 3 minutes 13 seconds after schedule time

4    1    5 103    07232020 11:10:00    07232020 11:15:12    O

-- This is out of range because its 5 minutes and 12 seconds after the scheduled time. Note it could be out of range if its earlier then the scheduled time too.


5    1    5 100    07232020 11:11:00     NULL     N

-- This row is NO GOOD as there is no corresponding access_history record. Note the NULL value for access_date 

这里有一种方法来解决这个问题:

-加入日程表
-外部加入这些访问历史记录
-仅包括计划日期内的行/-一些分钟数
-使用大小写表达式检查时间表和访问日期之间的差异，以分配状态

挑战在于选择以下时间范围:

-足够大，可以非常早或晚地捕捉人
-足够小，以至于您不会意外地开始包括访问，这实际上是针对下一个/上一个计划的

我已经走了10分钟的窗口，适用于这些数据:

select sa.employee_id,  
       sd.location_id,
       sd.schedule_date,
       ah.access_date,
       numtodsinterval ( ( schedule_date - access_date ), 'day' ) time_diff,
       case
         when abs ( schedule_date - access_date ) * 1440 <= 2 then 'GOOOOD'
         when ( schedule_date - access_date ) * 1440 between 2 and 4 then 'Early'
         when ( schedule_date - access_date ) * 1440 between -4 and -2 then 'Late'
         when access_date is not null then 'OUT OF RANGE'
         else 'NO GOOD!'
       end on_time
from   schedule_assignment sa
join   schedule_detail sd
on     sd.schedule_id = sa.schedule_id
left join access_history ah
on     sa.employee_id = ah.employee_id 
and    sd.location_id = ah.location_id
and    ah.access_date between sd.schedule_date - interval '10' minute
                      and sd.schedule_date + interval '10' minute
order by schedule_date;

EMPLOYEE_ID   LOCATION_ID SCHEDULE_DATE       ACCESS_DATE         TIME_DIFF            ON_TIME        
          1           100 07232020 11:00:00   07232020 11:00:20   -00 00:00:20.000000  GOOOOD          
          1           101 07232020 11:04:00   07232020 11:01:53   +00 00:02:07.000000  Early           
          1           102 07232020 11:07:00   07232020 11:10:13   -00 00:03:13.000000  Late            
          1           103 07232020 11:10:00   07232020 11:15:12   -00 00:05:12.000000  OUT OF RANGE    
          1           100 07232020 11:11:00                                NO GOOD! 

但是位置100的预定时间之间只有11分钟。如果员工第一次到达此位置的时间是一分钟后，他们将在下一次访问中显示为 “超出范围” (而不是未显示):

update access_history
set    access_date = access_date + 1/1440
where  location_id = 100;

select sa.employee_id,  
       sd.location_id,
       sd.schedule_date,
       ah.access_date,
       numtodsinterval ( ( schedule_date - access_date ), 'day' ) time_diff,
       case
         when abs ( schedule_date - access_date ) * 1440 <= 2 then 'GOOOOD'
         when ( schedule_date - access_date ) * 1440 between 2 and 4 then 'Early'
         when ( schedule_date - access_date ) * 1440 between -4 and -2 then 'Late'
         when access_date is not null then 'OUT OF RANGE'
         else 'NO GOOD!'
       end on_time
from   schedule_assignment sa
join   schedule_detail sd
on     sd.schedule_id = sa.schedule_id
left join access_history ah
on     sa.employee_id = ah.employee_id 
and    sd.location_id = ah.location_id
and    ah.access_date between sd.schedule_date - interval '10' minute
                      and sd.schedule_date + interval '10' minute
order by schedule_date;

EMPLOYEE_ID   LOCATION_ID SCHEDULE_DATE       ACCESS_DATE         TIME_DIFF              ON_TIME        
          1           100 07232020 11:00:00   07232020 11:01:20   -00 00:01:20.000000    GOOOOD          
          1           101 07232020 11:04:00   07232020 11:01:53   +00 00:02:07.000000    Early           
          1           102 07232020 11:07:00   07232020 11:10:13   -00 00:03:13.000000    Late            
          1           103 07232020 11:10:00   07232020 11:15:12   -00 00:05:12.000000    OUT OF RANGE    
          1           100 07232020 11:11:00   07232020 11:01:20   +00 00:09:40.000000    OUT OF RANGE    

总的来说，这是一个很难解决的问题。您如何确定某人是一次访问迟到还是下一次访问早？