Operand Data Type Time Is Invalid for Avg Operator...

Operand data type time is invalid for avg operator...?

You can use DateDiff( ms, '00:00:00', e.Duration ) to convert the time into an integer number of milliseconds. Use that for your aggregate, then convert the result back, e.g. Cast( DateAdd( ms, 1234, '00:00:00' ) as Time ).

Operand data type varchar is invalid for avg operator

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

Operand data type varchar(max) is invalid for avg operator

Based on the naming conventions in the queries, it is very reasonable to assume that StartChatTime and LastChatTime are in b and not a. If so, then the WHERE clause is turning the LEFT JOIN into an INNER JOIN.

In addition, including b.AgentId in the GROUP BY is redundant, because the ON clause specifies that it should be the same as a.id.

Storing numbers in strings is a bad, bad design. But you should probably use one of the try_() functions if you are stuck with a bad, bad design.

Let me assume that ChatDurationMinute is an integer. Then:

SELECT a.id, a.name, a.Report
       COUNT(DISTINCT b.pyID), 
       COUNT(DISTINCT b.SourceID), 
       AVG(TRY_CONVERT(int, b.ChatDurationMinute) * 1.0)
FROM table_1 a LEFT JOIN
     table_b b 
     ON a.id = b.agentid AND
        b.StartChatTime >= '20200701'' AND 
        b.LastChatTime <= '20200831'  
GROUP BY a.id, a.name, a.Report;

If those dates are really in a, then you can keep them in the WHERE clause.

Operand data type nchar is invalid for avg operator

0.24 won't convert to an int as it has decimal part.

You need to do CAST([size] as DECIMAL(9,2)) or some such...

Although we could really do with seeing your code :)

To use this CAST as part of an aggregate...

SELECT [database], AVG(CAST([size] as DECIMAL(9,2))) AS [Average of Size]
FROM table
GROUP BY [database]

Of course I don't know what your table or query actually is...

As others have said - if you are are going to be taking the average anyway then you will be better off not converting the number to an NVARCHAR or VARCHAR in the first place and working with the plain numeric fields.

As gbn notes this is 0,24 continental format so...

SELECT [database], AVG(CAST(REPLACE([size],',','.') as DECIMAL(9,2))) AS [Average of Size]
FROM table
GROUP BY [database]

Operand data type numeric is invalid for '~' operator

You need to cast it to bigint

UPDATE Table
SET attrEx= attrEx & (~CAST(576460752303423488 AS bigint) )
where attrEx != 0

This is documented here

Functions return bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

...snip...

Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type.

Operand data type time is invalid for subtract operator

I am certain the DATEDIFF works, but what I learned was that my bigger issue was an error in data where STARTDATE was later than ENDDATE, providing a negative answer. So I needed to add a clause in there to only do my math when ENDDATE > STARTDATE

IF OBJECT_ID('staging.dbo.Log_Batch_Report_2', 'U') IS NOT NULL
merge into staging.dbo.log_batch_report_2 a
using 
(select batch,
    starttime,
    endtime,
    solver_starttime,
    solver_endtime,
    CASE WHEN endtime>starttime THEN convert(time(0),(isnull(endtime,''))-(isnull(starttime,''))) 
        ELSE null
    END as totalruntime,
    CASE WHEN solver_endtime>solver_starttime THEN convert(time(0),(isnull(solver_endtime,''))-(isnull(solver_starttime,''))) 
        ELSE null
    END as solverruntime,
    CASE WHEN endtime>starttime and solver_endtime>solver_starttime and (endtime-starttime)>(solver_endtime-solver_starttime) 
            THEN convert(time(0),((isnull(endtime,'')-isnull(starttime,''))-((isnull(solver_endtime,'')-isnull(solver_starttime,''))))) 
        ELSE null
    END as non_solverruntime,
    to_time
from staging.dbo.log_batch_report
) b
on a.batch=b.batch and a.starttime=b.starttime and a.endtime=b.endtime
when matched then update set a.batch=a.batch
when not matched then insert (batch,batchdate,logility_up_time,solver_starttime,solver_endtime,starttime,endtime,totalruntime,solverruntime)
values (b.batch,b.starttime,b.endtime,b.solver_starttime,b.solver_endtime,b.starttime,b.endtime,b.totalruntime,b.solverruntime);

This may not be technically correct, but thus far, it is generating the difference in hours:minutes:seconds format that I am looking for, even if the times are on different days

---

Related Topics