ITNinja

Sounds like a classic use of ROLLUP to do the work:


SELECT IFNULL(MTH.MONTH,'Yearly') MONTH,IFNULL(U.USER_NAME,'Total====') User,
COUNT(T.ID) 'Total ticket closed'
FROM
USER U
JOIN USER_LABEL_JT OL ON OL.USER_ID=U.ID
JOIN LABEL L ON L.ID=OL.LABEL_ID and L.NAME LIKE '%' /*label name here */
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.LABEL_ID=OL.LABEL_ID
JOIN HD_QUEUE Q ON Q.ID=QOL.HD_QUEUE_ID and Q.NAME LIKE '%' /* queue name here */
LEFT JOIN (select 1 MONTH UNION
select 2 MONTH UNION
select 3 MONTH UNION
select 4 MONTH UNION
select 5 MONTH UNION
select 6 MONTH UNION
select 7 MONTH UNION
select 8 MONTH UNION
select 9 MONTH UNION
select 10 MONTH UNION
select 11 MONTH UNION
select 12 MONTH) MTH ON 1=1
LEFT JOIN HD_TICKET T ON T.OWNER_ID=U.ID and Q.ID=T.HD_QUEUE_ID and T.OWNER_ID<>0 and MONTH(T.TIME_CLOSED)=MTH.MONTH and YEAR(T.TIME_CLOSED)=YEAR(CURDATE())
LEFT JOIN HD_STATUS S ON (S.ID =T.HD_STATUS_ID) and S.STATE IN ('closed')

GROUP BY MTH.MONTH, U.USER_NAME
WITH ROLLUP

Thanks So Much!!!!

This is excellent! How can we turn this into a graph? Also, i'd like to show FULL_NAME instead of USER_NAME and be able to specify certain individuals. I found how to limit by the label, but i'd like to further limit by the name. For example. person A, B, C, D and E all work in tech and so they all have the same label and are on this list but person A, B and D are on one team and B and E are on another. I'd like to narrow down the list to only person A, B, and D.

Thank you.

You could this just above the group by:
WHERE U.FULL_NAME IN ('DAVID CHRISTIAN','BOB SMITH')

I would like to see this work but it is not reporting the correct data it seems.
1. I was able to modify the Queue Name to narrow down the results to the proper queue like this: JOIN HD_QUEUE Q ON Q.ID=QOL.HD_QUEUE_ID and Q.NAME LIKE 'ASD20 Help%' /* queue name here */
2. I was not able to narrow down the results for the User label "IT" with this modification: JOIN LABEL L ON L.ID=OL.LABEL_ID and L.NAME LIKE 'IT' /*label name here */
3. Since the User label wasn't working I am able to narrow the list by adding user names in the Where clause above group as suggested by dchristian. I'm assuming I can add the rest of the IT department members too when it is fully working: WHERE U.FULL_NAME IN ('RICH BATTIN','GINA LACY')
4. The main problem is the data collected for the last 12 months is not correct after month 3. After month 3 all entries are zero and that's not right. Is Month 1 the previous month and Month 2 just 2 months ago or are they relative to the 12 months asked for?
5. I'd really just like to summarize the IT workers help desk tickets closed by month for the 2010/2011 school year (August 2010 - July 2011. Is there a way to modify this for more months or that range of months?

Here is what I have so far:
SELECT IFNULL(MTH.MONTH,'Yearly') MONTH,IFNULL(U.USER_NAME,'Total====') User,
COUNT(T.ID) 'Total tickets'
FROM
USER U
JOIN USER_LABEL_JT OL ON OL.USER_ID=U.ID
JOIN LABEL L ON L.ID=OL.LABEL_ID and L.NAME LIKE '%' /*label name here */
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.LABEL_ID=OL.LABEL_ID
JOIN HD_QUEUE Q ON Q.ID=QOL.HD_QUEUE_ID and Q.NAME LIKE 'ASD20 Help%' /* queue name here */
LEFT JOIN (
select 1 MONTH UNION
select 2 MONTH UNION
select 3 MONTH UNION
select 4 MONTH UNION
select 5 MONTH UNION
select 6 MONTH UNION
select 7 MONTH UNION
select 8 MONTH UNION
select 9 MONTH UNION
select 10 MONTH UNION
select 11 MONTH UNION
select 12 MONTH) MTH ON 1=1
LEFT JOIN HD_TICKET T ON T.OWNER_ID=U.ID and Q.ID=T.HD_QUEUE_ID and T.OWNER_ID<>0
and MONTH(T.TIME_CLOSED)=MTH.MONTH and YEAR(T.TIME_CLOSED)=YEAR(CURDATE())
LEFT JOIN HD_STATUS S ON (S.ID =T.HD_STATUS_ID) and S.STATE IN ('closed')
WHERE U.FULL_NAME IN ('RICH BATTIN','GINA LACY')
GROUP BY MTH.MONTH, U.USER_NAME
WITH ROLLUP

The Month corresponds to the month of the year (January = 1).

Here's what you'll need to change if you want look at different years:
AND YEAR (T.TIME_CLOSED) = YEAR (CURDATE())

AND YEAR (T.TIME_CLOSED) = YEAR (CURDATE())
OK, so I would add that line to the WHERE statement and substitue a year like 2011 like this?:

WHERE U.FULL_NAME IN ('RICH BATTIN','GINA LACY')
AND YEAR (T.TIME_CLOSED) = 2011 (CURDATE())

AND YEAR (T.TIME_CLOSED) in (2011,2012)

ORIGINAL: dchristian
AND YEAR (T.TIME_CLOSED) in (2011,2012)
When I add this to the end of the WHERE clause I still only get 3 months of data (2012 first three months). When I change it to AND YEAR (T.TIME_CLOSED) in (2011) it finds no results.

Wierd...

Does this give your results?
SELECT
*
FROM
HD_TICKET
WHERE
YEAR (TIME_CLOSED) = 2011

ORIGINAL: dchristian

Wierd...

Does this give your results?
SELECT
*
FROM
HD_TICKET
WHERE
YEAR (TIME_CLOSED) = 2011

Yes, a report with just that in it took a while to generate but does list all 17,316 tickets closed in 2011.

This report works to find the tickets closed for those two users in 2012: SELECT IFNULL(MTH.MONTH,'Yearly') MONTH,IFNULL(U.USER_NAME,'Total====') User,
COUNT(T.ID) 'Total tickets closed'
FROM
USER U
JOIN USER_LABEL_JT OL ON OL.USER_ID=U.ID
JOIN LABEL L ON L.ID=OL.LABEL_ID and L.NAME LIKE '%' /*label name here */
JOIN HD_QUEUE_OWNER_LABEL_JT QOL ON QOL.LABEL_ID=OL.LABEL_ID
JOIN HD_QUEUE Q ON Q.ID=QOL.HD_QUEUE_ID and Q.NAME LIKE 'ASD20 Help%' /* queue name here */
LEFT JOIN (
select 1 MONTH UNION
select 2 MONTH UNION
select 3 MONTH UNION
select 4 MONTH UNION
select 5 MONTH UNION
select 6 MONTH UNION
select 7 MONTH UNION
select 8 MONTH UNION
select 9 MONTH UNION
select 10 MONTH UNION
select 11 MONTH UNION
select 12 MONTH) MTH ON 1=1
LEFT JOIN HD_TICKET T ON T.OWNER_ID=U.ID and Q.ID=T.HD_QUEUE_ID and T.OWNER_ID<>0 and MONTH(T.TIME_CLOSED)=MTH.MONTH and YEAR(T.TIME_CLOSED)=YEAR(CURDATE())
LEFT JOIN HD_STATUS S ON (S.ID =T.HD_STATUS_ID) and S.STATE IN ('closed')
WHERE (U.FULL_NAME IN ('RICH BATTIN','GINA LACY'))
GROUP BY MTH.MONTH, U.USER_NAME
WITH ROLLUP

but if I modify the WHERE line like this it finds no results:

WHERE (U.FULL_NAME IN ('RICH BATTIN','GINA LACY')) AND (YEAR (TIME_CLOSED) = 2011)

*scratches head.....

What about these:
SELECT *
FROM HD_TICKET H,
USER U
WHERE H.OWNER_ID = U.ID
AND YEAR(TIME_CLOSED) = 2011
AND U.FULL_NAME IN ('RICH BATTIN','GINA LACY')

Or even this:
SELECT *
FROM HD_TICKET H,
USER U
WHERE H.OWNER_ID = U.ID
AND U.FULL_NAME IN ('RICH BATTIN','GINA LACY')

SELECT *
FROM HD_TICKET H,
USER U
WHERE H.OWNER_ID = U.ID
AND YEAR(TIME_CLOSED) = 2011
AND U.FULL_NAME IN ('RICH BATTIN','GINA LACY')
This works and lists all tickets for those two for 2011. Yeah! Now I need to put that with the whole rollup thing for totals by month for last year...

I appreciate your help on this and am ashamed I don't do MySQL better then YourSQL. ;)

I'm still unable to figure out this simple request. It is just like the original poster's request with a minor modification:

I need a HelpDesk report that contains the following:
Total Closed Tickets By Owner by month for the specified year.
At the bottom of each month a grand total of Closed Tickets for all owners for the month.
A grand total of all Closed Tickets for the year.