Operand data type varchar is invalid for avg operator

I keep getting the above error message

“Operand data type varchar is invalid for avg operator”

Can anyone fix it for me? PLEASE

WITH Average -- Calculating Mean
AS (
    SELECT avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Average
    FROM [CLERKS]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    )
    ,data
AS (
    SELECT cast(ARRIVAL_DATE_TIME AS DATE) AS Attendance_Date
        ,avg(convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) / 3600) + ':' + convert(VARCHAR(5), DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 3600 / 60) + ':' + convert(VARCHAR(5), (DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME]) % 60))) AS Arr_Com
    FROM [Clerks]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    GROUP BY cast(ARRIVAL_DATE_TIME AS DATE)
    )
SELECT a.Attendance_Date
    ,a.Arr_Com
    ,c.Average
    ,abs(a.Arr_Com - b.Arr_Com) AS MR
FROM data a
LEFT JOIN data b ON cast(a.Attendance_Date AS DATETIME) = cast(b.Attendance_Date AS DATETIME) + 1
CROSS JOIN Average c
ORDER BY a.Attendance_Date
GO

Thanks in Advance

Answer

Actually, the problem is that the implementation is incorrect. You don’t want to average timestamps (5h:3m:20s), but durations.

Hence, you need to calculate the duration in the smallest denominator, in your case seconds, calculate the average in seconds, by using the AVG() function and then formatting that result to look like hh:mm:ss.

Your code should look like:

;WITH Average -- Calculating Mean
AS (
    SELECT AVG(DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME])) as Average
    FROM [CLERKS]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    )
    ,data
AS (
    SELECT cast(ARRIVAL_DATE_TIME AS DATE) AS Attendance_Date
        , AVG(DateDiff(s, [ARRIVAL_DATE_TIME], [COMPLETE_DATE_TIME])) as Arr_Com
    FROM [Clerks]
    WHERE [ARRIVAL_DATE_TIME] >= DATEADD(dd, - 30, getdate() - 1)
    GROUP BY cast(ARRIVAL_DATE_TIME AS DATE)
    )
SELECT
    Attendance_Date
    , Arr_Com
    , Average
    , avg(convert(VARCHAR(5), MR / 3600) + ':' + convert(VARCHAR(5), MR % 3600 / 60) + ':' + convert(VARCHAR(5), MR % 60))) AS MR
FROM (
    SELECT a.Attendance_Date
        ,a.Arr_Com
        ,c.Average
        , abs(a.Arr_Com - b.Arr_Com) AS MR
    FROM data a
    LEFT JOIN data b ON cast(a.Attendance_Date AS DATETIME) = cast(b.Attendance_Date AS DATETIME) + 1
    CROSS JOIN Average c
    ) tmp
ORDER BY Attendance_Date