Hello, peeps. I am looking for a way to make two reports with this funcionality:

1) It counts the total number of cases registered the past month (from the 10. to the 10. easch month)? An alterred version will count and display the number of cases registered each calendar month the current year.

2) It counts cases registered on the various categories the past month, also from the 10. to 10. each month. Alternatively a colomn, row or simular tells the amount of cases registered the past month like described in 1).

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

If you are trying to aggregate by year, month, queue, category, then try the following query as a starting point.  Modify as appropriate to your setup.  Note that our "months" run from the 25th to the 24th and is keyed to the ticket create date.  We also have custom fields for "FCR" (yes/no) and intake "channel" (single select with values like email, phone, in person, etc.).  Also, we are only interested in some of the queues for this report.

You can open the output in Excel and create a pivot table to slice, dice, and analyze.

I hope this helps.

SELECT
   CASE
 -- december to january
        when ( day(T.CREATED) in (25,26,27,28,29,30,31) and month(T.CREATED) = 12 ) then 1
-- add to month
        when ( day(T.CREATED) in (25,26,27,28,29,30,31) ) then month(T.CREATED) + 1
-- else just month
else month(T.CREATED) END as scorecard_month,
    CASE 
-- december to january
        when ( day(T.CREATED) in (25,26,27,28,29,30,31) and month(T.CREATED) = 12 ) then year(T.CREATED) + 1
-- else just month
else year(T.CREATED) END as scorecard_year,
    HD_QUEUE.ID,
    HD_QUEUE.NAME as 'Queue',
    HD_CATEGORY.NAME as 'Category',
    T.CUSTOM_FIELD_VALUE2 AS 'FCR?',
    T.CUSTOM_FIELD_VALUE1 AS 'channel',
    count(T.id) as 'Count'
FROM
    HD_QUEUE
INNER JOIN
    HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
    INNER JOIN HD_CATEGORY ON T.HD_CATEGORY_ID = HD_CATEGORY.ID
where T.CREATED between '2013-05-25' and '2014-05-24' and
  HD_QUEUE.id in(1,4,5,7,8,9,10,12,13,14,16)
GROUP BY scorecard_year, scorecard_month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE2, T.CUSTOM_FIELD_VALUE1
ORDER BY scorecard_year, scorecard_month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE2, T.CUSTOM_FIELD_VALUE1;
 

 

Answered 05/28/2014 by: grayematter
Fourth Degree Black Belt

  • Thanks. Its very helpfull. I have altered your script somewhat. I have excluded coloms that I dont need and changed the "month" to be from the 11th to the 10th. Have I done so right?

    [code]
    SELECT
    CASE
    -- december to january
    when ( day(T.CREATED) in (11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) and month(T.CREATED) = 12 ) then 1
    -- add to month
    when ( day(T.CREATED) in (11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) ) then month(T.CREATED) + 1
    -- else just month
    else month(T.CREATED) END as Month,
    CASE
    -- december to january
    when ( day(T.CREATED) in (11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31) and month(T.CREATED) = 12 ) then year(T.CREATED) + 1
    -- else just month
    else year(T.CREATED) END as Year,
    HD_QUEUE.NAME as 'Queue',
    HD_CATEGORY.NAME as 'Category',
    count(T.id) as 'Count'
    FROM
    HD_QUEUE
    INNER JOIN
    HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
    INNER JOIN HD_CATEGORY ON T.HD_CATEGORY_ID = HD_CATEGORY.ID
    where T.CREATED between '2013-01-01' and '2014-06-31' and
    HD_QUEUE.id in(1,4,5,7,8,9,10,12,13,14,16)
    GROUP BY Year, Month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE2, T.CUSTOM_FIELD_VALUE1
    ORDER BY Year, Month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE2, T.CUSTOM_FIELD_VALUE1;
    [/code]
    • We could probably change the case from "in (11,12, ...." to " > 10" in both of our queries. Make sure the HD_QUEUE.ID values are appropriate for your environment. Also, make sure the columns in the group by and order by sections match what you are selecting. If you aren't selecting custom field values, remove them.
      • Thanks for your feedback. I have changed the SQl statement and think this is better:

        SELECT
        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,
        HD_QUEUE.NAME as 'Queue',
        HD_CATEGORY.NAME as 'Category',
        count(T.id) as 'Count'
        FROM
        HD_QUEUE
        INNER JOIN
        HD_TICKET T ON HD_QUEUE.ID = T.HD_QUEUE_ID
        INNER JOIN HD_CATEGORY ON T.HD_CATEGORY_ID = HD_CATEGORY.ID
        where T.CREATED between '2013-01-01' and '2014-06-31' and
        HD_QUEUE.id in(1,4,5,7,8,9,10,12,13,14,16)
        GROUP BY Year, Month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE1
        ORDER BY Year, Month, HD_QUEUE.NAME,HD_CATEGORY.NAME, T.CUSTOM_FIELD_VALUE1;


        When you say "Make sure the HD_QUEUE.ID values are appropiate for your enviroment" you talk about the statement third to last "HD_QUEUE.id in(1,4,5,7,8,9,10,12,13,14,16)"?
      • That's it exactly. Those are IDs for the queues on which you wish to report. You can get the values from the HD_QUEUE table.
Please log in to comment
Answer this question or Comment on this question for clarity