Get Average from SQL Server Query Results

Lorrec
Lorrec used Ask the Experts™
on
Hello. I have modified a  SQL Server query that I got from a friend to output the total execution for each process. It outputs the results as 00:00:00 and the datatype is varchar. I want to combine all the results and get an average. However, I cannot figure out how to convert it from varchar to decimal so I can get the average. Any assistance is appreciated.

Current Results
Total
00:52:34
01:31:33
0:15:27

Desired Results
Total
00:53:11

Query
;WITH CTETotal AS (SELECT CASE  
	WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN 
	CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS varchar(2)) + 'd ' 
	ELSE '' 
	END + CONVERT(varchar(8), tslast.Time - tsstart.Time, 108) Total
FROM 
( 
	SELECT 
	MAX(ExecutionTime) AS Time, 
	AdvertisementID, 
	ResourceID 
	FROM v_TaskExecutionStatus 
	WHERE LastStatusMessageID = 11140 AND Step = 0 and
	ExecutionTime  > DATEADD(month, -1,GETDATE())
	GROUP BY AdvertisementID, ResourceID 
) tsstart  
INNER JOIN 
( 
	SELECT 
	AdvertisementID, 
	ResourceID, 
	LastStatusTime as Time 
	FROM v_ClientAdvertisementStatus
	WHERE LastState = 13 AND
	AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND 
tsstart.ResourceID = tslast.ResourceID AND 
tslast.Time > tsstart.Time)
SELECT Total
FROM CTETotal

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
No sample data, but maybe?

;WITH CTETotal AS (SELECT
      CASE WHEN AvgSecs >= 86400 THEN CAST(AvgSecs / 86400 AS varchar(20)) + 'd '
    ELSE ''
    END + CONVERT(varchar(8), DATEADD(SECOND, AvgSecs % 86400, 0)) Total
FROM (
    SELECT  
    CEILING(SUM(DATEDIFF(SECOND, tsstart.Time, tslast.Time)) * 1.0 / COUNT(*)) AS AvgSecs
FROM (
(
      SELECT
      MAX(ExecutionTime) AS Time,
      AdvertisementID,
      ResourceID
      FROM v_TaskExecutionStatus
      WHERE LastStatusMessageID = 11140 AND Step = 0 and
      ExecutionTime  > DATEADD(month, -1,GETDATE())
      GROUP BY AdvertisementID, ResourceID
) tsstart  
INNER JOIN
(
      SELECT
      AdvertisementID,
      ResourceID,
      LastStatusTime as Time
      FROM v_ClientAdvertisementStatus
      WHERE LastState = 13 AND
      AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND
tsstart.ResourceID = tslast.ResourceID AND
tslast.Time > tsstart.Time)) AS final)
SELECT Total
FROM CTETotal

Author

Commented:
Thank you for the response.  I receive the below output.

Total
Jan  1 1
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
OOPS, I used the wrong format code in the output -- 0 instead of 108.  Here's corrected:

;WITH CTETotal AS (SELECT
      CASE WHEN AvgSecs >= 86400 THEN CAST(AvgSecs / 86400 AS varchar(20)) + 'd '
    ELSE ''
    END + CONVERT(varchar(8), DATEADD(SECOND, AvgSecs % 86400, 108)) Total
FROM (
    SELECT  
    CEILING(SUM(DATEDIFF(SECOND, tsstart.Time, tslast.Time)) * 1.0 / COUNT(*)) AS AvgSecs
FROM (
(
      SELECT
      MAX(ExecutionTime) AS Time,
      AdvertisementID,
      ResourceID
      FROM v_TaskExecutionStatus
      WHERE LastStatusMessageID = 11140 AND Step = 0 and
      ExecutionTime  > DATEADD(month, -1,GETDATE())
      GROUP BY AdvertisementID, ResourceID
) tsstart  
INNER JOIN
(
      SELECT
      AdvertisementID,
      ResourceID,
      LastStatusTime as Time
      FROM v_ClientAdvertisementStatus
      WHERE LastState = 13 AND
      AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND
tsstart.ResourceID = tslast.ResourceID AND
tslast.Time > tsstart.Time)) AS final)
SELECT Total
FROM CTETotal
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

awking00Information Technology Specialist

Commented:
;WITH CTETotal AS (SELECT CASE  
	WHEN DATEDIFF(dd, tsstart.Time, tslast.Time) > 0 THEN 
	CAST(DATEDIFF(dd, tsstart.Time, tslast.Time) AS varchar(2)) + 'd ' 
	ELSE '' 
	END + CONVERT(varchar(8), tslast.Time - tsstart.Time, 108) Total
FROM 
( 
	SELECT 
	MAX(ExecutionTime) AS Time, 
	AdvertisementID, 
	ResourceID 
	FROM v_TaskExecutionStatus 
	WHERE LastStatusMessageID = 11140 AND Step = 0 and
	ExecutionTime  > DATEADD(month, -1,GETDATE())
	GROUP BY AdvertisementID, ResourceID 
) tsstart  
INNER JOIN 
( 
	SELECT 
	AdvertisementID, 
	ResourceID, 
	LastStatusTime as Time 
	FROM v_ClientAdvertisementStatus
	WHERE LastState = 13 AND
	AdvertisementID = 'MXXXXX'
) tslast ON tsstart.AdvertisementID = tslast.AdvertisementID AND 
tsstart.ResourceID = tslast.ResourceID AND 
tslast.Time > tsstart.Time),
CTE2 as
(select cast(left(total,2) as int) * 3600 +
 cast(substring(4,2) as int) * 60 +
 cast(right(total,2) as int as totsecs
 FROM CTETotal),
CTE3 as
(select 
right('00' + cast(floor(avgsecs/3600) as varchar(2)),2) + ':' +
right('00' + cast(floor(avgsecs/60) - floor(avgsecs/3600) as varchar(2)),2)
right('00' + cast(avgsecs - (floor(avgsecs/60) - floor(avgsecs/3600)) * 60 as varchar(2)),2) as avgtot
from cte3;

Open in new window

Author

Commented:
Thank you for the assistance. Much appreciated.
awking00Information Technology Specialist

Commented:
This line needs a parenthesis -
cast(right(total,2) as int) as totsecs

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial