2

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(card_date || ' ' || card_time, 'YYYY/MM/DD HH:MI:SS AM') AS card_timestamp -- 使用TO_TIMESTAMP进行转换 FROM AUSZDLMS.h_ata_record WHERE card_date >= current_date - 1 -- 查询当天数据 ), ptms_combined AS ( SELECT e.emp_no, e.emp_name_ch, e.emp_dept, e.class_id, e.class_no, p.card_date, p.card_time, p.F1_SJ, p.location_id, p.in_out, p.card_timestamp, LAG(p.card_timestamp) OVER (PARTITION BY e.emp_no ORDER BY p.card_timestamp) AS prev_card_timestamp FROM emp_info e LEFT JOIN ptms_info p ON e.emp_no = p.emp_no WHERE p.card_time IS NOT NULL AND p.location_id NOT LIKE '%F1-1F%' AND p.location_id NOT LIKE '%F1-2F%' AND 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;