如何在SQL及SSRS报表中计算考勤系统的Time_In平均值？

一、在SQL查询中添加Time_In平均值计算 #

现有查询用于获取当日考勤记录，同时统计员工当月迟到（Time_In >= '09:15:00'）的次数。要添加员工当月截至当日的Time_In平均值，可以沿用现有子查询的逻辑，替换为AVG函数并处理时间类型转换：

修改后的完整SQL代码如下：

SELECT
c.gregorian_date,
c.WORKING_DAY,
c.notes,
a.am_empno AS emp_no,
t.emp_name,
a.Time_In,
a.Time_Out,
a.am_att_status,
t.team_name,
t.line_manager,
(SELECT COUNT(a2.Time_In)
 FROM pp_emp_att_mst_v a2
 WHERE a2.am_empno = a.am_empno
   AND a2.Time_In >= '09:15:00'
   AND a2.am_date BETWEEN TRUNC(SYSDATE, 'mm') AND TRUNC(SYSDATE)
) AS Time_In_MTD,
-- 新增：员工当月截至当日的Time_In平均值
(SELECT TO_CHAR(AVG(TO_DATE(a2.Time_In, 'HH24:MI:SS')), 'HH24:MI:SS')
 FROM pp_emp_att_mst_v a2
 WHERE a2.am_empno = a.am_empno
   AND a2.am_date BETWEEN TRUNC(SYSDATE, 'mm') AND TRUNC(SYSDATE)
   AND a2.Time_In IS NOT NULL -- 排除无打卡记录的空值
) AS Avg_Time_In_MTD
FROM
dim_calendar c
JOIN pp_emp_att_mst_v a ON c.gregorian_date = a.am_date AND a.am_date = TRUNC(SYSDATE)
JOIN team_names t ON a.am_empno = t.EMP_NO
WHERE
c.gregorian_date = TRUNC(SYSDATE)
AND a.Time_In >= '09:15:00'
AND c.gregorian_date BETWEEN TRUNC(SYSDATE, 'mm') AND TRUNC(SYSDATE)

• 若Time_In字段本身是DATE类型（而非字符串），可简化为TO_CHAR(AVG(a2.Time_In), 'HH24:MI:SS')
• 如果需要计算团队级别的当日平均打卡时间，可以用窗口函数替代子查询：

  AVG(TO_DATE(a.Time_In, 'HH24:MI:SS')) OVER (PARTITION BY t.team_name) AS Team_Avg_Time_In
  

二、在SSRS报表中实现Time_In平均值计算 #

方法1：直接使用SQL返回的平均值 #

将SQL中计算好的Avg_Time_In_MTD或Team_Avg_Time_In字段直接拖入报表对应位置即可，无需额外处理。

方法2：在SSRS中通过表达式计算 #

如果不想修改SQL，可直接基于报表原始字段计算：

• 员工月均打卡时间：在员工分组的组脚注添加文本框，表达式为：

  =Format(Avg(DateValue("1900-01-01") + TimeValue(Fields!Time_In.Value)), "HH:mm:ss")
  

• 团队当日平均打卡时间：在团队分组的组脚注添加文本框，表达式为：

  =Format(Avg(DateValue("1900-01-01") + TimeValue(Fields!Time_In.Value)), "HH:mm:ss")
  

• 全局总平均打卡时间：在报表页脚添加文本框，使用上述相同表达式即可。

注：若SSRS已自动识别Time_In为时间类型，可简化表达式为=Format(Avg(Fields!Time_In.Value), "HH:mm:ss")

内容的提问来源于stack exchange，提问作者Michael Benning