Calculate average of time difference between two dates

i want to Calculate average and sum of time difference between two dates in DD:HH:MM:YY format of multiple records,

i tried this

WITH cte AS

(

SELECT

wt.workorder_transaction_id,

wt.workorder_id,

wt.updated_at,

wt.status_id

,ROW_NUMBER ( )OVER (PARTITION BY workorder_id ORDER BY workorder_transaction_id ) AS rowno

FROM workorder_transaction wt

INNER JOIN workorder_status ws ON ws.status_id = wt.status_id

WHERE LOWER(ws.status) = 'qc pending'

AND workorder_id IN(

SELECT workorder_id FROM workorder_transaction wt

INNER JOIN workorder_status ws ON ws.status_id = wt.status_id

WHERE LOWER(ws.status) ='qc approved'

)

,cte1 AS

(

SELECT

STUFF(CONVERT(VARCHAR(50),wt.updated_at-cte.updated_at,114),1,2,DATEDIFF(hh,0,wt.updated_at-cte.updated_at))

AS TimeDiff

FROM cte

INNER JOIN workorder_transaction wt ON wt.workorder_id = cte.workorder_id

WHERE rowno = 1 And wt.status_id = 6

GROUP BY cte.workorder_transaction_id,cte.workorder_id,cte.updated_at,cte.status_id

,wt.status_id,wt.updated_at

)

SELECT

CONVERT(CHAR(8),DATEADD(SECOND,SUM ( DATEPART(hh,(CONVERT(DATETIME,TimeDiff,1))) * 3600 +

DATEPART(mi, (CONVERT(DATETIME,TimeDiff,1))) * 60 + DATEPART(ss,(CONVERT(DATETIME,TimeDiff,1)))),0),108)

AS total_sum,

CONVERT(CHAR(8),DATEADD(SECOND,AVG ( DATEPART(hh,(CONVERT(DATETIME,TimeDiff,1))) * 3600 +

DATEPART(mi, (CONVERT(DATETIME,TimeDiff,1))) * 60 + DATEPART(ss,(CONVERT(DATETIME,TimeDiff,1)))),0),108)

AS average

FROM cte1

when hours get above 24hr SQL server not handle the situation and give error as

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

can you please provide the solution

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
I obviously, didn't read all of this and didn't understand much of it, but please just let me know this: are you trying to arrive at days, hours, minutes and seconds from an integer number?

If yes, there is no direct way of doing this. You will need to write a custom function which would do the job (to the best of my knowledge and understanding)

In that function, you write a logic to divide that number and arrive at each part separately and keep on reducing the number as you progress though the calculation.

Hope you get the point. If not, please do let me know.
+1
Nitin Sontakke8y ago
@Naushad,

You seem to have used a MySql function. The question is related to MS SQL Server.
+1
Naushad Akhtar8y ago
1. SELECT DATE(start) AS startdate, AVG(TIME_TO_SEC(TIMEDIFF(end,start))) AS timediff FROM Sessions GROUP BY startdate
0