KACE Product Support

I took what Chuck suggested and changed the last ORDER BY statement to sort the months chronologically instead of alphabetically (then by owner).  Also spaced the code out a little bit so I could understand the flow a little better.

One other idea - when you create the SQL report, you can also set the Break on Columns to MONTH if you want things broken down by month a little differently - creates nice separation for a visual report.

Now if I only knew how to get numbers to align left in the report (like the text), I'd be completely satisfied.  Guess I'll have to research that a bit.

Hope this helps!

John

__________________________

SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED

FROM  (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
FROM  HD_TICKET T                
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP  BY OWNER_ID, MONTH, YEAR        
ORDER  BY YEAR, MONTH) OPEN
        
LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.TIME_CLOSED, '%M') AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
FROM  HD_TICKET T                    
JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP  BY OWNER_ID, MONTH, YEAR
ORDER  BY YEAR, MONTH) CLOSED

ON( OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
WHERE OPEN.YEAR = date_format(curdate(), '%Y')
ORDER BY str_to_date(OPEN.MONTH,'%M'), OWNER

This is working well for me except

1. I haven't been able to narrow it down by ticket queue ID, we have many.

2. I need to understand what the OPEN.OWNER represents. . .as in;

I have multiple ticket owners in the queue, a New ticket has no owner assigned on save, a rule assigns an owner, that owner may change it to another owner and so on. At any given time a single ticket could have two or three owners assigned at different times prior to it's being closed.

I need the answer to #2 because a songle Tech complained his opened count was too low ;)

Thanks

jmarotto

You should be able to add this to the WHERE statement (before the ORDER BY statement) to specify the queue (#3 in this example):

and HD_TICKET.HD_QUEUE_ID = 3

Just change the "3" to whatever queue you want.

Based on the code, OPEN.OWNER refers to the ticket owner at the time the report is run.  Try running a MySQL query on ticket owners specifying your tech and see if that matches up.  If you need help with that, see if this post might help get you started (particularly section 7):

http://www.itninja.com/blog/view/primer-for-writing-select-statement-queries-on-the-k1000-w-custom-sql-report-example

John

To only include the current year, add the following line before the last ORDER BY 3,2,1

WHERE OPEN.YEAR = date_format(curdate(), '%Y')

I was able to get the months displayed with their text name instead of number by altering the select statements. If you change the instances of:

MONTH(T.CREATED)   AS MONTH

to 

date_format(T.TIME_CLOSED,'%M') AS MONTH

it displays the month names, but the sorting is then incorrect (because it is based on the alphabetical month names, not their order in the calendar). You could possibly get around that by using a case statement to change how the OPEN.MONTH column is displayed in the end, but there might be a better way to do that. 

Also, in case this might be helpful for someone, here's the same report but without the current year restriction, sorted by year, month (chronological) and finally by owner, with the year and month sorted descending (so the newest stuff is at the top).

John

_____________________________

SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, Coalesce(OPEN.OPEN, 0) AS OPENED, Coalesce(CLOSED.CLOSED, 0) AS CLOSED

FROM  (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.CREATED, '%M') AS MONTH, YEAR (T.CREATED) AS YEAR, COUNT(*) AS OPEN
FROM  HD_TICKET T                
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP  BY OWNER_ID, MONTH, YEAR        
ORDER  BY YEAR, MONTH) OPEN
        
LEFT JOIN (SELECT Coalesce(U.USER_NAME, 'NO OWNER ASSIGNED') AS OWNER, date_format(T.TIME_CLOSED, '%M') AS MONTH, YEAR (T.TIME_CLOSED) AS YEAR, COUNT(*) AS CLOSED
FROM  HD_TICKET T                    
JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
LEFT JOIN USER U ON T.OWNER_ID = U.ID
GROUP  BY OWNER_ID, MONTH, YEAR
ORDER  BY YEAR, MONTH) CLOSED

ON( OPEN.MONTH = CLOSED.MONTH AND OPEN.YEAR = CLOSED.YEAR AND OPEN.OWNER = CLOSED.OWNER )
ORDER BY YEAR desc, str_to_date(OPEN.MONTH,'%M') desc, OWNER