Get average of time in SQL or SSRS

Question

I am making an attendance system. Here in the code:

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,

**-- Here I want AVG(a.Time_In)**

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)

I want to get average of time_in.

I am also using this query in SSRS report, so if there's a way to get average of time_in in SSRS report please share.

Answer 1

You need to convert your time to a numeric value and then you can average it and convert it back to a time. Assuming that you want a similar average for each employee for the current month then you can use:

SELECT c.gregorian_date,
       a.am_empno AS emp_no,
       t.emp_name,
       a.Time_In,
       ( SELECT TO_CHAR(
                  TRUNC(SYSDATE)
                  + AVG(
                    TO_DATE(a2.Time_In, 'HH24:MI:SS')
                    - TO_DATE('00:00:00', '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)
       ) AS avg_time_in_for_current_month
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)