SELECT emp.employee_number, – 员工工号 emp.name, – 员工姓名 COALESCE(tran_dep.value, dep.name, '') AS department_name, COALESCE(tran_position.value, position.name, '') AS position_name, detail.attendance_date, – 如当天排班，且为不为休息，则为 排班结束（日模型结束）时间；如为休息，或者未排班 则为第一次打卡时间； 注意：主要时间不要日期，如 09:00 CASE WHEN detail.day_calendar_id IS NOT NULL OR daily_model.code='DM00' OR daily_model.code='DM52' THEN COALESCE(to_char(detail.clock_in_time,'hh24:mi'), '') ELSE daily_model.work_off_start END AS start_time, – 开始时间 – 如当天排班，且为不为休息，则为 排班结束（日模型结束） 后最后一次打卡时间；如为休息，或者未排班 则为最后一次打卡时间； 如只有1次打卡则为空；注意：主要时间不要日期，如 22:00 COALESCE(to_char(detail.clock_out_time,'hh24:mi'), '') AS end_time, – 结束时间 CASE WHEN detail.day_calendar_id IS NOT NULL OR daily_model.code='DM00' OR daily_model.code='DM52' THEN cast(round(cast(date_part('epoch', detail.clock_out_time::time - detail.clock_in_time::time)/60/60 as numeric ),1)as varchar) ELSE cast(round(cast(date_part('epoch', detail.clock_out_time::time - daily_model.work_off_start::time)/60/60 as numeric ),1)as varchar) END AS real_hour, – 实际时长 null AS overtime_reason, – 加班原因 空，不写逻辑 (detail.clock_in_place || ',' || detail.clock_out_place) AS app_place, – APP显示位置 开始时间的打卡位置 及 结束时间的打卡位置，中间用“逗号”分隔 COALESCE(tran_area.value, area.name, '') AS area_name, – 考勤区域 开始时间的考勤区域 及 结束时间的考勤区域，中间用“逗号”分隔 (detail.clock_in_dest_address || ',' || detail.clock_out_dest_address) AS dest_address – 考勤点 开始时间的考勤点 及 结束时间的考勤点，中间用“逗号”分隔 FROM hr_attendance_details detail LEFT JOIN hr_employee emp ON emp.id=detail.employee_id LEFT JOIN emp_job ON emp.job_info_id=emp_job.id – 部门 LEFT JOIN hr_department AS dep ON emp_job.department_id = dep.eroad_index_id AND dep.eroad_index_id IS NOT NULL AND dep.eroad_start_date <= detail.attendance_date AND (dep.eroad_end_date >= detail.attendance_date OR dep.eroad_end_date IS NULL) AND dep.active = TRUE LEFT JOIN ir_translation AS tran_dep on tran_dep.res_id=dep.id and tran_dep.lang='zh_CN' and tran_dep.name='hr.department,name' – 岗位 LEFT JOIN hr_position AS position ON emp_job.position_id = position.eroad_index_id AND position.eroad_index_id IS NOT NULL AND position.eroad_start_date <= detail.attendance_date AND (position.eroad_end_date >= detail.attendance_date OR position.eroad_end_date IS NULL) AND position.active = TRUE LEFT JOIN ir_translation AS tran_position on tran_position.res_id=position.id and tran_position.lang='zh_CN' and tran_position.name='hr.position,name' – 排班 LEFT JOIN day_calendar ON day_calendar.id=detail.day_calendar_id – 排班日模型 LEFT JOIN daily_model ON daily_model.id = day_calendar.daily_model_id – 考勤区域 LEFT JOIN hr_attendance_area area ON detail.attendance_area_id=area.id LEFT JOIN ir_translation AS tran_area on tran_area.res_id=area.id and tran_area.lang='zh_CN' and tran_area.name='hr.attendance.area,name'