SQL report to get average amount of time for helpdesk closures
-
Hi, I've been asked to write a report which lists the average time it takes for a helpdesk job to be closed. There isn't a closed date as such, the table column is simply an audit date/time which can be for various tasks. I need to use this column along with another which is an ID of the action. For example: User TaskID Task AuditDatetime Bob 2 Opens job 1/8/2014 11:14:29 Bob 5 Closes job 2/8/2014 10:12:26 Joe 2 Opens job 2/8/2014 11:01:45 Bob 5 Closes job 3/8/2014 11:44:22 My SQL is very rusty, I'm sure the report is technically possible but I can't for the life of me figure out what the SQL is. Can anybody please help? I'm aware of the SQL AVG function and also know I may need to GROUP BY the user. I'm thinking some kind of CASE statement perhaps but again I can't think of the SQL to achieve this...
-
Hi, I've been asked to write a report which lists the average time it takes for a helpdesk job to be closed. There isn't a closed date as such, the table column is simply an audit date/time which can be for various tasks. I need to use this column along with another which is an ID of the action. For example: User TaskID Task AuditDatetime Bob 2 Opens job 1/8/2014 11:14:29 Bob 5 Closes job 2/8/2014 10:12:26 Joe 2 Opens job 2/8/2014 11:01:45 Bob 5 Closes job 3/8/2014 11:44:22 My SQL is very rusty, I'm sure the report is technically possible but I can't for the life of me figure out what the SQL is. Can anybody please help? I'm aware of the SQL AVG function and also know I may need to GROUP BY the user. I'm thinking some kind of CASE statement perhaps but again I can't think of the SQL to achieve this...
Try something like this:
select avg(diff)
from
(
select datediff(d,h1.auditdatetime,h2.auditdatetime) diff
from helpdesk h1
join helpdesk h2
on h1.taskid = h2.taskid
and h2.task = 'closes job'
where h1.task = 'opens job'
)x“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Hi, I've been asked to write a report which lists the average time it takes for a helpdesk job to be closed. There isn't a closed date as such, the table column is simply an audit date/time which can be for various tasks. I need to use this column along with another which is an ID of the action. For example: User TaskID Task AuditDatetime Bob 2 Opens job 1/8/2014 11:14:29 Bob 5 Closes job 2/8/2014 10:12:26 Joe 2 Opens job 2/8/2014 11:01:45 Bob 5 Closes job 3/8/2014 11:44:22 My SQL is very rusty, I'm sure the report is technically possible but I can't for the life of me figure out what the SQL is. Can anybody please help? I'm aware of the SQL AVG function and also know I may need to GROUP BY the user. I'm thinking some kind of CASE statement perhaps but again I can't think of the SQL to achieve this...
This can be done in various ways but one way could be to use a small scalar query in the select statement. Something like:
SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job'Using a query like the above you can get the open/close pair data and you can use that as an inline view. So something like
SELECT AVG(DurationInHours)
FROM (SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job') ivProbably I made a ton of typos but hope you get the idea :) [EDIT] Just noticed that GuyThiebaut wrote almost same kind of answer at the same time. If the value of TaskId can be used to connect the open/close operations then his approach is better. The reason I used the date field is that in your example data the same TaskId was opened twice so my interpretation was that the TaskId isn't unique among tasks:
User TaskID Task AuditDatetime Bob 2 Opens job 1/8/2014 11:14:29 Bob 5 Closes job 2/8/2014 10:12:26 Joe 2 Opens job 2/8/2014 11:01:45 Bob 5 Closes job 3/8/2014 11:44:22
-
This can be done in various ways but one way could be to use a small scalar query in the select statement. Something like:
SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job'Using a query like the above you can get the open/close pair data and you can use that as an inline view. So something like
SELECT AVG(DurationInHours)
FROM (SELECT User AS OpenedBy,
AuditDatetime AS StartTime,
DATEDIFF(hh, AuditDatetime, ( SELECT MIN(AuditDatetime)
FROM TheTable tt2
WHERE tt2.AuditDatetime > tt.AuditDatetime)
)AS DurationInHours
FROM TheTable tt
WHERE tt.Task = 'Opens job') ivProbably I made a ton of typos but hope you get the idea :) [EDIT] Just noticed that GuyThiebaut wrote almost same kind of answer at the same time. If the value of TaskId can be used to connect the open/close operations then his approach is better. The reason I used the date field is that in your example data the same TaskId was opened twice so my interpretation was that the TaskId isn't unique among tasks:
User TaskID Task AuditDatetime Bob 2 Opens job 1/8/2014 11:14:29 Bob 5 Closes job 2/8/2014 10:12:26 Joe 2 Opens job 2/8/2014 11:01:45 Bob 5 Closes job 3/8/2014 11:44:22
Hi Mike - I made the assumption that the taskid is the key and that one person may open the task while another may close it. I don't think we have been given enough information to solve this without guessing ;)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Hi Mike - I made the assumption that the taskid is the key and that one person may open the task while another may close it. I don't think we have been given enough information to solve this without guessing ;)
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Sorry my data was bad. Hopefully this makes more sense: 1. The time a job takes for a person to close is the difference between when a job is assigned to a user and when that user closes the job. 2. A job can be re-opened which I guess means I need to use MIN and MAX for a given job. Here's better data: JobID User TaskID Task AuditDatetime 17 Bob 2 Opens job 1/8/2014 11:14:29 17 Bob 5 Closes job 2/8/2014 10:12:26 22 Joe 2 Opens job 2/8/2014 11:01:45 22 Bob 5 Closes job 3/8/2014 11:44:22 25 Bob 1 Opens job 4/8/2014 11:40:22 25 Joe 3 Assigned job 5/8/2014 11:47:22 25 Joe 5 Closes job 5/8/2014 11:49:22 The jobID is a request made to the service desk. The TaskID can be opens, assigns, closes. I'll only be able to report on jobs closed too I suppose rather than open jobs too I guess? This is actually pretty complicated as there's also on hold items but I will forget about these for now.
-
Sorry my data was bad. Hopefully this makes more sense: 1. The time a job takes for a person to close is the difference between when a job is assigned to a user and when that user closes the job. 2. A job can be re-opened which I guess means I need to use MIN and MAX for a given job. Here's better data: JobID User TaskID Task AuditDatetime 17 Bob 2 Opens job 1/8/2014 11:14:29 17 Bob 5 Closes job 2/8/2014 10:12:26 22 Joe 2 Opens job 2/8/2014 11:01:45 22 Bob 5 Closes job 3/8/2014 11:44:22 25 Bob 1 Opens job 4/8/2014 11:40:22 25 Joe 3 Assigned job 5/8/2014 11:47:22 25 Joe 5 Closes job 5/8/2014 11:49:22 The jobID is a request made to the service desk. The TaskID can be opens, assigns, closes. I'll only be able to report on jobs closed too I suppose rather than open jobs too I guess? This is actually pretty complicated as there's also on hold items but I will forget about these for now.
No problem, if that's the case then perhaps something like
SELECT iv.JobId,
iv.OpenedBy,
iv.ClosedBy,
iv.OpenTime,
iv.CloseTime,
DATEDIFF(hh, iv.OpenTime, iv.CloseTime) AS DurationInHours
FROM (
SELECT DISTINCT
tt.JobId AS JobId,
tt_open.User AS OpenedBy,
tt_close.User AS ClosedBy,
MIN(tt_open.AuditDatetime) OVER (PARTITION BY tt_open.JobId ORDER BY tt_open.AuditDatetime) AS OpenTime,
MAX(tt_close.AuditDatetime) OVER (PARTITION BY tt_close.JobId ORDER BY tt_close.AuditDatetime) AS CloseTime
FROM TheTable tt_open LEFT OUTER JOIN TheTable tt_close
ON tt_open.JobId = tt_close.JobId
WHERE tt_open.TaskId IN (1,2) -- both values seem to exist for open
AND tt_close.TaskId = 5) ivAgain, sorry about potential typos :)
-
No problem, if that's the case then perhaps something like
SELECT iv.JobId,
iv.OpenedBy,
iv.ClosedBy,
iv.OpenTime,
iv.CloseTime,
DATEDIFF(hh, iv.OpenTime, iv.CloseTime) AS DurationInHours
FROM (
SELECT DISTINCT
tt.JobId AS JobId,
tt_open.User AS OpenedBy,
tt_close.User AS ClosedBy,
MIN(tt_open.AuditDatetime) OVER (PARTITION BY tt_open.JobId ORDER BY tt_open.AuditDatetime) AS OpenTime,
MAX(tt_close.AuditDatetime) OVER (PARTITION BY tt_close.JobId ORDER BY tt_close.AuditDatetime) AS CloseTime
FROM TheTable tt_open LEFT OUTER JOIN TheTable tt_close
ON tt_open.JobId = tt_close.JobId
WHERE tt_open.TaskId IN (1,2) -- both values seem to exist for open
AND tt_close.TaskId = 5) ivAgain, sorry about potential typos :)