I am using this code below that I got from another post.  I was wondering if anyone could help me fix it to only show the current year's tickets, to name the months (ie "June" instead of "6") and to remove the opened section?

Also is it possible to make a sql report display into a graph like when you create a report thru the wizard?

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, MONTH(T.CREATED) 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, MONTH(T.TIME_CLOSED) 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 3,2,1
Answer Summary:
SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, 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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

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

Answered 06/19/2012 by: jverbosk
Red Belt

  • is there any way to get rid of the "Opened" Column without losing the "Closed" column?
  • Sure, the easiest way is to just remove the OPENED column from the first line, like this:

    SELECT OPEN.OWNER, OPEN.MONTH, OPEN.YEAR, 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

    John
Please log in to comment

Answers

0

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

Answered 06/26/2012 by: jmarotto
Second Degree Green Belt

Please log in to comment
0

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

Answered 06/26/2012 by: jverbosk
Red Belt

  • Thanks John,
    I've tried that one and tried using the existing T alias. Both return an Unknown column error in the Where statement. I've also tried to find a way to add it to the Select statement without success as well. I'm dumping it all into an Excel file and can parse out the unwanted rows but it's a bit of a pain when the dept mgrs. want it by queue.
  • Sorry, didn't see the HD_TICKET table was being aliased - that's what the FROM HD_TICKET T statement does (says to use "T" instead of "HD_TICKET" so you don't have to type it out every time). Try this:

    T.HD_QUEUE_ID = 3

    And just change the "3" to whatever queue you want.

    John
Please log in to comment
0

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. 


Answered 06/19/2012 by: chucksteel
Red Belt

Please log in to comment
0

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

Answered 06/19/2012 by: jverbosk
Red Belt

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