November 4, 2013 at 6:00 am
Hey Everyone,
I'm new to writing SQL queries and I'm a bit stumped in this one. The below query currently defines a total elapsed time of specific jobs that I have run.
SELECT distinct JOBNUM, CONVERT(varchar(6), DATEDIFF(second, began, ended)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, began, ended) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, began, ended) % 60), 2) AS 'Elapsed' from GECSJOBHISTORY
ORDER BY JOBNUM ASC
So there are multiple records for each JOBNUM, but I need the average out of this for each JOBNUM. I'm sure I need to use the COUNT and AVG functions, but I"m not sure how to work those in since the 'ELAPSED' field in the results isn't a true Column. I appreciate any input on this.
Thanks
Steve
November 4, 2013 at 6:43 am
Steve-0 (11/4/2013)
Hey Everyone,I'm new to writing SQL queries and I'm a bit stumped in this one. The below query currently defines a total elapsed time of specific jobs that I have run.
SELECT distinct JOBNUM, CONVERT(varchar(6), DATEDIFF(second, began, ended)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, began, ended) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, began, ended) % 60), 2) AS 'Elapsed' from GECSJOBHISTORY
ORDER BY JOBNUM ASC
So there are multiple records for each JOBNUM, but I need the average out of this for each JOBNUM. I'm sure I need to use the COUNT and AVG functions, but I"m not sure how to work those in since the 'ELAPSED' field in the results isn't a true Column. I appreciate any input on this.
Thanks
Steve
You can feed just about anything you want to to count and avg functions. In this case, you're looking for something like this...
SELECT JOBNUM, AVG(CONVERT(varchar(6), DATEDIFF(second, began, ended)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, began, ended) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, began, ended) % 60), 2)) AS 'Elapsed' from GECSJOBHISTORY
GROUP BY JOBNUM
ORDER BY JOBNUM ASC
November 4, 2013 at 6:53 am
Thanks for your reply Steven. I am getting the error below.
Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for avg operator.
I would have thought VARCHAR wouldn't be the issue here.
Thoughts.
Steve
November 4, 2013 at 7:05 am
Steve-0 (11/4/2013)
Hey Everyone,I'm new to writing SQL queries and I'm a bit stumped in this one. The below query currently defines a total elapsed time of specific jobs that I have run.
SELECT distinct JOBNUM, CONVERT(varchar(6), DATEDIFF(second, began, ended)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, began, ended) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, began, ended) % 60), 2) AS 'Elapsed' from GECSJOBHISTORY
ORDER BY JOBNUM ASC
So there are multiple records for each JOBNUM, but I need the average out of this for each JOBNUM. I'm sure I need to use the COUNT and AVG functions, but I"m not sure how to work those in since the 'ELAPSED' field in the results isn't a true Column. I appreciate any input on this.
Thanks
Steve
First point is that your DISTINCT guarantees that if two runs of a job take the same number of elapsed seconds you will only see one of them - you won't see all your runs.
To get averages, it will be easier to the averaging first using teh raw elapsed time in seconds, then make the pretty format. Something like
;
WITH elapsedTimes as (select Jobnum, DATEDIFF(second, began, ended) as elapsedsecs from GECSJOBHISTORY),
elapsedAVGs as (select JobNum, AVG(elapsedsecs) as AvgTimefrom elapsedTimes group by JobNum)
select A.Jobnum, AvgTime, elapsedsecs/3600+':'+right('z0'+((elapsedsecs%3600)/60),2))+':'+right('z0+(elapsedsecs%60).2)
from elapsedTimes T inner join elapsedAvgs A on A.Jobnum = T.Jobnum
;
gives you the job number, the average elapsed run time in seconds for that jobnumber, and the pretty format elapsed time for one run of that jobnumber is a row, with one row for each run. Of course if you just want the average, you can leave off the individual time and don''t need the join with the unaveraged data; and if you want the average time prettified, you can do it exactly as you did the individual times.
Tom
November 4, 2013 at 7:07 am
Oh yeah, you have the convert in there, which returns a VARCHAR type. So even though it's displaying numbers, the datatype is VARCHAR.
You can move the AVG function directly around your datediff functions...
SELECT JOBNUM, CONVERT(varchar(6), AVG(DATEDIFF(second, began, ended))/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), (AVG(DATEDIFF(second, began, ended)) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2), AVG(DATEDIFF(second, began, ended)) % 60), 2) AS 'Elapsed' from GECSJOBHISTORY
GROUP BY JOBNUM
ORDER BY JOBNUM ASC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply