I am a neewbie and need some SQL-help for a report.

I am trying to make a report that displays number of cases thats opened/made the last "month" (the "month" last from the 11th to the 10th), how many of the cases that's closed/finished and number of cases that are unasigned to either a category or person (the default category is "Other :: Møterom"). I guess its easer to display if it is unasigned to a person.

Instead of making indivudial reports for different months a report for the whole year with separation for the different months is ok.

 

 

2 Comments   [ + ] Show Comments

Comments

  • I set my system up with the default category of "Please select a Category..." That makes uncategorized tickets very obvious and I use that as part of my selection criteria in reporting.

    As you are looking for counts, for unassigned tickets try using the following as a column in your query. Make sure that OWNER_NAME is in your Group By clause as well

    ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME
  • By having report(s) that says which categorys are not in use its easier to delete them.
Please log in to comment

Answers

0

This was a fun one:

SELECT
COUNT(HD_TICKET.ID) as "Tickets Opened",
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
SUM(CASE WHEN HD_CATEGORY.NAME = 'Unknown' THEN 1 ELSE 0 END) as "Unassigned Category",
SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
FROM HD_TICKET  
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
AND MONTH(HD_TICKET.CREATED) = MONTH(DATE_SUB(NOW(),INTERVAL 1 MONTH))
AND YEAR(HD_TICKET.CREATED) = YEAR(DATE_SUB(NOW(),INTERVAL 1 MONTH))

The above selects from the previous month. The following shows tickets grouped by year and month:

SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,
COUNT(HD_TICKET.ID) as "Tickets Opened",
SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
SUM(CASE WHEN HD_CATEGORY.NAME = 'Unknown' THEN 1 ELSE 0 END) as "Unassigned Category",
SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
FROM HD_TICKET  
JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
WHERE (HD_TICKET.HD_QUEUE_ID = 2)
GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED)

Note that you'll need to change this to reflect your default category (mine is Unknown so look for that in the query). You'll also need to change the HD_TICKET.HD_QUEUE_ID = 2 to match your queue's ID.

Answered 06/03/2014 by: chucksteel
Red Belt

  • Thanks for Your quick response. I have changed the Query as you can see below. The months that we want to report on is from the 11th to the 10th. I know this problaby makes the SQL Query more Advanced but how do I do that?

    SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,
    COUNT(HD_TICKET.ID) as "Tickets Opened",
    SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open",
    SUM(CASE WHEN HD_CATEGORY.NAME = 'Other :: Møterom' THEN 1 ELSE 0 END) as "Unassigned Category",
    SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker"
    FROM HD_TICKET
    JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID)
    LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID)
    WHERE (HD_TICKET.HD_QUEUE_ID = 1)
    GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED)
    • In order to limit the month reported you would need to add statements to the WHERE clause of the statement. For instance, you could add
      MONTH(HD_TICKET.CREATED) = 10 to limit to tickets that were created in that month. If you wanted a range of dates you could use something like
      HD_TICKET.CREATED BETWEEN '2013-10-01' and '2013-11-30'

      To add these to the above statement you would place them after WHERE (HD_TICKET.HD_QUEUE_ID = 1) and the GROUP BY clause, so you would have something like this:

      SELECT YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH, COUNT(HD_TICKET.ID) as "Tickets Opened", SUM(CASE WHEN TIME_CLOSED = '0000-00-00 00:00:00' THEN 1 ELSE 0 END) as "Still Open", SUM(CASE WHEN HD_CATEGORY.NAME = 'Other :: Møterom' THEN 1 ELSE 0 END) as "Unassigned Category", SUM(CASE WHEN HD_TICKET.OWNER_ID = 0 THEN 1 ELSE 0 END) as "Unassigned Worker" FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 1)
      and HD_TICKET.CREATED BETWEEN '2013-10-01' and '2013-11-30'
      GROUP BY YEAR(HD_TICKET.CREATED), MONTH(HD_TICKET.CREATED)

      Does that help?
    • If you are referring to the "month" construct from http://www.itninja.com/question/reports-in-kace-that-counts-number-of-cases, you can substitute the same query segments for YEAR and MONTH. So change

      YEAR(HD_TICKET.CREATED) as YEAR, MONTH(HD_TICKET.CREATED) as MONTH,

      to

      CASE
      -- december to january
      when ( day(T.CREATED) >10 and month(T.CREATED) = 12 ) then 1
      -- add to month
      when ( day(T.CREATED) > 10) then month(T.CREATED) + 1
      -- else just month
      else month(T.CREATED) END as Month,
      CASE
      -- december to january
      when ( day(T.CREATED) > 10 and month(T.CREATED) = 12 ) then year(T.CREATED) + 1
      -- else just month
      else year(T.CREATED) END as Year,
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share