Average Days Open Report-Conversion to Kace
Can someone please help me convert this sql statement from my Old helpdesk into a Kace report. I am trying to calculate average days open.
Select
help_category,cast(isnull(AVG(CAST(case when help_status = 'OPEN'
then GETDATE()
when help_status = 'CLOSED'
then help_close_datetime end - [help_datetime] AS float)),0) as decimal(7,3))
as average_days
FROM [HelpDesk].[dbo].[hd_help_desk]
where (help_status
<> 'CLOSED' or (help_status = 'CLOSED' and cast(help_datetime AS date) =
cast(help_close_datetime AS date) and cast(help_datetime AS date) = cast(GETDATE()-1 as DATE))) and help_priority
not in('6')
group by help_category
order by
help_category
I believe I am close, any and all assistance is helpful....
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Just as there are many ways to bake a cake, so are there may ways to do this via SQL. Two are listed below.
Just as there are many ways to bake a cake, so are there may ways to do this via SQL. Two are listed below.
Please log in to answer
Posted by:
chucksteel
9 years ago
This is how I would do it:
SELECT HD_CATEGORY.NAME as "Category",
count(HD_TICKET.ID) as "Tickets Closed",
AVG(TIMESTAMPDIFF(DAY,
TIME_OPENED,
TIME_CLOSED)
) AS "Average Days"
FROM ORG1.HD_TICKET
JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
WHERE HD_TICKET.HD_QUEUE_ID = 2
and TIME_CLOSED != '0000-00-00 00:00:00'
GROUP BY HD_CATEGORY_ID
ORDER BY HD_CATEGORY.NAME
;
Note that this query restricts to tickets in queue 2 (HD_TICKET.HD_QUEUE_ID = 2). You may need to modify that for your appliance.
Comments:
Posted by:
Jbr32
9 years ago
Here is another option, as implemented in the Twitter Bootstrap for Kace project http://www.itninja.com/blog/view/twitter-bootstrap-for-kace-service-desk
<pre>
SELECT count(HD_TICKET.ID) as total,
HD_TICKET.HD_CATEGORY_ID as CatID,
HD_CATEGORY.NAME as CatName,
SUM(IF(HD_STATUS.NAME not like '%closed%',1,0)) as currently_open,
SUM(IF(HD_TICKET.CREATED >= DATE_SUB(NOW(), INTERVAL 30 DAY),1,0)) as openedLast30,
SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 30 DAY),1,0)) as closedLast30,
SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 30 DAY),
TIMESTAMPDIFF(SECOND,HD_TICKET.CREATED,HD_TICKET.TIME_CLOSED),0))
/ SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 30 DAY),1,0)) as avg30_s,
SUM(IF(HD_TICKET.CREATED >= DATE_SUB(NOW(), INTERVAL 12 MONTH),1,0)) as openedLast12,
SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 12 MONTH),1,0)) as closedLast12,
SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 12 MONTH),
TIMESTAMPDIFF(SECOND,HD_TICKET.CREATED,HD_TICKET.TIME_CLOSED),0))
/ SUM(IF(HD_TICKET.TIME_CLOSED >= DATE_SUB(NOW(), INTERVAL 12 MONTH),1,0)) as avg12m_s
FROM HD_TICKET
JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID)
LEFT JOIN HD_CATEGORY ON (HD_TICKET.HD_CATEGORY_ID=HD_CATEGORY.ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_STATUS.NAME not like '%Server Status Report%')
AND (HD_STATUS.NAME not like '%spam%')
AND (HD_TICKET.HD_QUEUE_ID = 10)
AND (
((HD_STATUS.STATE not like '%closed%')
AND HD_TICKET.CREATED >= DATE_SUB(DATE_ADD(last_day(NOW()), INTERVAL 1 DAY), INTERVAL 12 MONTH))
OR (HD_TICKET.TIME_CLOSED >= DATE_SUB(DATE_ADD(last_day(NOW()), INTERVAL 1 DAY), INTERVAL 12 MONTH))
)
GROUP BY HD_TICKET.HD_CATEGORY_ID
ORDER BY SUM(IF(HD_STATUS.NAME not like '%closed%',1,0)) DESC
</pre>
