我有一个名为access\u history的表，它跟踪员工向传感器出示访问卡的位置、卡号和日期时间。
目前，员工每天都用考勤卡打卡，记录上班时间和下班时间。是的，我知道那是一种非常过时的方法。这就是为什么我被抛出这个项目来捕获这些数据，并将其转换为一个时间和出勤系统。
我在考虑使用触发器来实现这一点，但我认为有两个问题。首先，已有数据需要转换。第二，如果触发器被禁用或删除，我将丢失数据，并在同步时变为out。所以我认为最好的方法是浏览数据并创建出勤记录。
作为一个dba，而不是一个开发人员，我想最好的方法是使用merge语句，也就是upsert。如果记录在那里，请更新它，否则请插入它。顺便说一句，如果这不是最好的方法，我愿意接受任何和所有的建议，包括设计更改。
请记住，我有大约1万名员工和大约一百万以上的历史记录，每天，我需要扫描，所以请随意添加任何钥匙，partitions…
我知道可能有很多插入和更新，因为我正在为每个员工更新emp\u info表。任何关于避免这种情况的想法都是很好的。
请注意，emp\ U考勤表中可能有1对以上的开始和结束日期，因为人们可能会出去吃午饭或去不同的建筑。其次，由于我们是全天候的，所以结束日期可以跨越午夜。在我的示例测试用例中，我提供了一个这种情况的示例。
我希望有人能给我提供一些代码或让我开始如何遍历和配对的数据。
我知道我需要查询emp\u history表，其中location\u type=t，并查看employees表中的date>last/start\u date
感谢所有帮助我们的人。更多的细节和细节会更好。感谢所有的回答。

-- Drop table emp_info purge:
-- Drop table locations purge;
-- Drop table access_histoty purge;
-- Drop table emp_attendance purge;

CREATE TABLE employees
(
   employee_id NUMBER(6),
   first_name VARCHAR2(20),
   last_name VARCHAR2(25) NOT NULL,
   card_num varchar2(10)  NOT NULL,
   Last_start_date DATE
);

ALTER TABLE employees
     ADD ( CONSTRAINT employee_id_pk
   PRIMARY KEY (employee_id));

Insert into employees values (1, 'Mike', 'Jones', 'AAA1', NULL);

Insert into employees values (2, 'Jane', 'Doe', 'BBB2', NULL);

Insert into employees values (3, 'Paul', 'Smith', 'CCC3', NULL);

Insert into employees values (4, 'John', 'Henry', 'DDD4', NULL);

 Create table locations(
   location_id NUMBER(4),
   location_name varchar2(30),
   location_type char(1));

 -- A=access T=Time & Attendance 

ALTER TABLE locations 
     ADD ( CONSTRAINT lication_id_pk
   PRIMARY KEY (location_id));

Insert into locations values (101, 'South Front Door 1', 'T');

  Insert into locations values (102, 'South Front Door 2', 'T');

  Insert into locations values (103, 'East Back Door 1', 'T');

   Insert into locations values (104, 'East Back Door 2', 'T');

   Insert into locations values (105,'Computer Room', 'A');

   Insert into locations values (106,'1st Floor North',  'A');

Create table access_history(
   employee_id NUMBER(6), 
   card_num varchar2(10),
   location_id number(4),
   Access_date date
);

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (1, 'AAA1', 101,    TO_DATE('06212020 21:02:04',  'MMDDYYYY HH24:MI:SS'));

   -- TYpe T no previous data for this
   -- empid record INSERT empid, 
    -- start  time ONLY in table below 
     -- and  update last_start_date  
     -- with DATETIME.

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (1, 'AAA1', 102,    TO_DATE('06212020 23:52:14',  'MMDDYYYY HH24:MI:SS'));

  -- Type T record empid, start_time
  --  set update end_time only in
  -- emp_attendance.

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (2, 'BBB2', 103,    TO_DATE('06212020 08:32:35',  'MMDDYYYY HH24:MI:SS'));

 -- TYpe T  INSERT empid, start 
 -- time ONLY in emp_attendance.  
 -- update last_start_date with 
 --  DATETIME on emp_info table

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (2, 'BBB2', 102,    TO_DATE('06212020 15:39:05',  'MMDDYYYY HH24:MI:SS'));

-- Type T record empid, start_time 
--  set, update end_time only in
-- emp_attendance.

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (3, 'CCC3', 103,    TO_DATE('06212020 15:39:05',  'MMDDYYYY HH24:MI:SS'));

 -- TYpe T  INSERT empid, start 
 -- time ONLY in emp_attendance.  
 -- update last_start_date with 
 --  DATETIME on emp_info table

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (3, 'CCC3', 105,    TO_DATE('06212020 18:19:55',  'MMDDYYYY HH24:MI:SS'));

-- Type A record don't do anything to 
-- emp_attendance.

INSERT INTO access_history
( employee_id, card_num,
   location_id, Access_date )
 VALUES (3, 'CCC3', 104,    TO_DATE('06222020 04:04:35',  'MMDDYYYY HH24:MI:SS'));

-- Type T record empid, start_time 
--  set, update end_time only in
-- emp_attendance.

-- After the first run the output 
-- should.   look like this:

 CREATE TABLE  emp_attendance    
  (employee_id NUMBER(6),
   start_date DATE,
   end_date DATE
   create_date DATE
   );

  Create sequence emp_attendance_seq;

insert into emp_attendance (seq_num, employee_id, start_date, end_Date)
   with
   prep (employee_id, start_date, rn, end_date) as (
     select employee_id, access_date
        , row_number()      over (partition by card_num order by access_date)
      , lead(access_date) over (partition by card_num order by access_date)
   from   access_history
 where  location_id in ( select location_id
                     from   locations
                      where  location_type = 'T'
                  )
  )
  select 
    emp_attendance_seq.nextval,
      employee_id,
     start_date,
     nvl(end_date, start_date)
    from prep
   where  mod(rn, 2) = 1;

1  06212020.   06212020  SYSDATE
    21:02:04.     23:52:14

2   06212020.  06212020  SYSDATE
 08:32:35        15:39:05 

3  06212020   06222020.  SYSDATE 
    15:39:05     04:04:35

  --- changes
  -- INSERT records
  -- if end_date is NULL set to start date
    --  Added sequence number to row for unique identification