0

I am trying to find an average time to complete a task for a given period, for example, between 2022-07-01 and 2022-07-23. My table has received and completed date timestamps columns as shown below. My queries work, but I want to combine my two separate queries into one. It would also be helpful if someone could help me with a simpler method to get my desired result.

Received_date Completed_date
2022-07-01 14:06:16.000 2022-07-01 14:06:00.000
2022-07-01 14:03:48.000 2022-07-01 14:04:00.000
. .
. .
2022-07-23 09:05:25.000 2022-07-23 10:53:00.000

Code 1 -Average time in seconds to complete request

SELECT AVG(ABS(DATEDIFF(SECOND, Completed_date, Received_date )) ) as 'Average time in seconds to complete request' 
FROM Mytable 
WHERE Completed_date >='2022-07-01 00:00' and Completed_date <= '2022-07-23 23:59'

Output
Average time in seconds to complete request
149408

Code 2 -Average time to complete a task for a given period

DECLARE @AVG_TIME DATETIME = DATEADD(SECOND, 149408, 0) 
SELECT ( CAST(DATEPART(MONTH, @AVG_TIME) - 1 AS VARCHAR(2)) + ' month(s) ' + CAST(DATEPART(DAY, @AVG_TIME) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @AVG_TIME) AS VARCHAR(2)) + ' hour(s) '+ CAST(DATEPART(MINUTE, @AVG_TIME) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @AVG_TIME) AS VARCHAR(2)) + ' second(s)' ) as 'Average time to complete a task for a given period'

Output
Average time to complete a task for a given period
0 month(s) 1 day(s) 17 hour(s) 30 minute(s) 8 second(s)

Upcheers
  • 5
  • 2
  • You could make these both subqueries and then join on the ID, assuming that's a viable key. That would join your results together. – jw11432 Aug 29 '22 at 17:41
  • Sorry for the confusion. The two tables above are one table. It has several columns, including the received_date and completed_date. I had an issue putting the table together when I was typing out my question. – Upcheers Aug 29 '22 at 19:30

1 Answers1

0

This is what I mean. It combines both your queries, regardless of the source table.

DECLARE @AVG_TIME DATETIME = DATEADD(SECOND, 149408, 0) 

;With CTE1 as (
SELECT ID, AVG(ABS(DATEDIFF(SECOND, Completed_date, Received_date )) ) as 'Average time in seconds to complete request' 
FROM Mytable 
WHERE Completed_date >='2022-07-01 00:00' and Completed_date <= '2022-07-23 23:59')


,CTE2 as (
SELECT ID, ( CAST(DATEPART(MONTH, @AVG_TIME) - 1 AS VARCHAR(2)) + ' month(s) ' + CAST(DATEPART(DAY, @AVG_TIME) - 1 AS VARCHAR(2)) + ' day(s) ' + CAST(DATEPART(HOUR, @AVG_TIME) AS VARCHAR(2)) + ' hour(s) '+ CAST(DATEPART(MINUTE, @AVG_TIME) AS VARCHAR(2)) + ' minute(s) ' + CAST(DATEPART(SECOND, @AVG_TIME) AS VARCHAR(2)) + ' second(s)' ) as 'Average time to complete a task for a given period'
)

select *
from CTE1
join CTE2 on cte1.ID = CTE2.ID
jw11432
  • 545
  • 2
  • 20