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....

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.
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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.
Answered 09/04/2014 by: chucksteel
Red Belt

  • ChuckSteel,

    thank you for your help. This is what I needed.
Please log in to comment
0
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>
Answered 09/04/2014 by: Jbr32
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share