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