3

WITH emp_info AS (
    SELECT emp_no, emp_name_ch, emp_dept, class_id, class_no
    FROM AUSZDLMS.i_facs_employee
    WHERE emp_dept LIKE 'MS01%'
),
ptms_info AS (
    SELECT emp_no, 
           card_date, 
           card_time,
           card_time AS F1_SJ,
           import_date, 
           location_id, 
           in_out,
           TO_TIMESTAMP(TO_CHAR(card_date, 'YYYY-MM-DD') || ' ' || card_time, 'YYYY-MM-DD HH24:MI:SS') AS card_timestamp  -- 将 card_time 转换为 24 小时制
    FROM AUSZDLMS.h_ata_record
    WHERE card_date >= current_date - 1  -- 查询当天数据
),
ptms_combined AS (
    SELECT p.*, 
           LAG(card_timestamp) OVER (PARTITION BY emp_no ORDER BY card_timestamp) AS prev_card_timestamp
    FROM ptms_info p
    WHERE p.location_id NOT LIKE 'F1-3F%'
      AND p.location_id NOT LIKE 'F1-4F%'
      AND p.location_id NOT LIKE 'F2-3F%'
      AND p.location_id NOT LIKE 'F2-4F%'
)
SELECT emp_no,
       emp_name_ch,
       emp_dept,
       class_id,
       class_no,
       card_date,
       card_time,
       F1_SJ,
       prev_card_timestamp,
       EXTRACT(EPOCH FROM (card_timestamp - prev_card_timestamp)) / 60 AS time_diff_minutes,
       location_id,
       in_out
FROM ptms_combined
WHERE prev_card_timestamp IS NOT NULL
  AND EXTRACT(EPOCH FROM (card_timestamp - prev_card_timestamp)) / 60 > 30
ORDER BY emp_no, card_date, F1_SJ;