ITNinja

There are two assumptions that we need clarification on and my hunch is that one of them is tripping many of you up:

The first is more major and probably wrong in most cases. The queries above are assuming that a ticket with a TIME_CLOSED value are closed. However, TIME_CLOSED is merely the last timestamp when a ticket was moved from a non-closed state into a closed state. A ticket that was reopened (for any reason including a mistake) will have a TIME_CLOSED value >0 but be in an opened state.

To query on tickets in a closed state you need to query on HD_STATUS.STATE='Closed' joined through HD_STATUS_ID

Secondly, a minor one, the original question asks:
... total number of tickets open...
which we are all assuming to be "tickets created", but technically a ticket is usually created into a stalled state and not opened until moved into a open state at some point. I believe this to be a correct assumption but worth noting just in case.

So the modification with the adjusted assumptions would end up with:
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

If these assumptions are not correct then please clarify any requests.

How can I change this to run on seperate Queues. this is what I am looking for but I have 10 queues and 10 different managers.

Thanks
Lyle

For each queue you could have a separate query or you could group the results by queue. I'm guessing the former because you have 10 different managers implying 10 different reports?

Just for the sake of keeping it similar to the original report:

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 ,
T.HD_QUEUE_ID
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 ,
T.HD_QUEUE_ID
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.HD_QUEUE_ID IS NULL OR OPEN.HD_QUEUE_ID IN (1,0)) /*change queue numbers here*/
and (CLOSED.HD_QUEUE_ID IS NULL OR CLOSED.HD_QUEUE_ID IN (1,0)) /*change queue numbers here*/
ORDER BY 3,2,1

Exactly what I need

Thanks
Lyle

How would I take the reports in this thread and have them so that the months showed up as names instead of numbers?

Thanks!

nslatter,

Try this.

I added the monthname function to the T.CREATED field.

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,
MONTHNAME(T.CREATED) AS MONTH,
MONTH(T.CREATED) AS MONTHSORT,
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN ,
T.HD_QUEUE_ID
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,
MONTHNAME(T.TIME_CLOSED) AS MONTH,
YEAR (T.TIME_CLOSED) AS YEAR,
COUNT(*) AS CLOSED ,
T.HD_QUEUE_ID
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.HD_QUEUE_ID IS NULL OR OPEN.HD_QUEUE_ID IN (1,0)) /*change queue numbers here*/
and (CLOSED.HD_QUEUE_ID IS NULL OR CLOSED.HD_QUEUE_ID IN (1,0)) /*change queue numbers here*/
ORDER BY YEAR, MONTHSORT, OWNER]

dchristian,

Thanks for your help. This gives me the name of the month but when I change that ( and I changed only that ) it causes the closed numbers to reflect 0. I even created a brand new report and copied this into it to ensure that I wasn't doing something incorrectly.

That doesn't make sense to me but I'm not versed at all in SQL so any work within it is a learning experience for me.

Again, thanks for your help.

Nate

You right!

I corrected the statement, please try again.

That word dchristian. Thanks for your help!

Hey guys,

This is a report that I am looking to create, however I need this report to show me the total number of tickets opened and total number of tickets closed for the last 7 days.

My plan would be to set the report to get these results for the last 7 days, and schedule the report to run at 00:00 every Friday.

How would I go about modifying this SQL Query to show the results for the last 7 days, and instead of showing the total number of opened and closed by owner, just show total number all up, or have a total row at the bottom?

Thanks guys, hope you can help

Cheers
Dave

ORIGINAL: GillySpy

There are two assumptions that we need clarification on and my hunch is that one of them is tripping many of you up:

The first is more major and probably wrong in most cases. The queries above are assuming that a ticket with a TIME_CLOSED value are closed. However, TIME_CLOSED is merely the last timestamp when a ticket was moved from a non-closed state into a closed state. A ticket that was reopened (for any reason including a mistake) will have a TIME_CLOSED value >0 but be in an opened state.

To query on tickets in a closed state you need to query on HD_STATUS.STATE='Closed' joined through HD_STATUS_ID

Secondly, a minor one, the original question asks:
... total number of tickets open...
which we are all assuming to be "tickets created", but technically a ticket is usually created into a stalled state and not opened until moved into a open state at some point. I believe this to be a correct assumption but worth noting just in case.

So the modification with the adjusted assumptions would end up with:
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

If these assumptions are not correct then please clarify any requests.

dchristian,
I tried modifying your SQL to show monthly but minus the owner piece yet still specifying queue 3 and 7, but the result is skipping some months and seems to still be grabbing across all queues.  Any ideas?

SELECT OPEN.MONTH,
 OPEN.YEAR,
 Coalesce(OPEN.OPEN, 0) AS OPENED,
 Coalesce(CLOSED.CLOSED, 0) AS CLOSED
 FROM (SELECT MONTHNAME(T.CREATED) AS MONTH,
 MONTH(T.CREATED) AS MONTHSORT,
 YEAR (T.CREATED) AS YEAR,
 COUNT(*) AS OPEN ,
 T.HD_QUEUE_ID
 FROM HD_TICKET T
 GROUP BY MONTH,
 YEAR
 ORDER BY YEAR,
 MONTH) OPEN
 LEFT JOIN (SELECT MONTHNAME(T.TIME_CLOSED) AS MONTH,
 YEAR (T.TIME_CLOSED) AS YEAR,
 COUNT(*) AS CLOSED ,
 T.HD_QUEUE_ID
 FROM HD_TICKET T 
 JOIN HD_STATUS S ON HD_STATUS_ID=S.ID and S.STATE ='Closed'
 GROUP BY MONTH,
 YEAR
 ORDER BY YEAR,
 MONTH) CLOSED
 ON( OPEN.MONTH = CLOSED.MONTH
 AND OPEN.YEAR = CLOSED.YEAR )
 WHERE (OPEN.HD_QUEUE_ID IS NULL OR OPEN.HD_QUEUE_ID IN (3,7)) /*change queue numbers here*/
 and (CLOSED.HD_QUEUE_ID IS NULL OR CLOSED.HD_QUEUE_ID IN (3,7)) /*change queue numbers here*/
 ORDER BY YEAR, MONTHSORT

Hey Chris,

Is this what your looking for?
SELECT OPEN.MONTH,
OPEN.YEAR,
Coalesce(OPEN.OPEN, 0) AS OPENED,
Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT MONTH(T.CREATED) AS MONTH,
YEAR (T.CREATED) AS YEAR,
COUNT(*) AS OPEN
FROM HD_TICKET T
GROUP BY MONTH,
YEAR
ORDER BY YEAR,
MONTH) OPEN
LEFT JOIN (SELECT MONTH(T.TIME_CLOSED) AS MONTH,
YEAR (T.TIME_CLOSED) AS YEAR,
COUNT(*) AS CLOSED
FROM HD_TICKET T
WHERE T.TIME_CLOSED NOT LIKE '0000%'
GROUP BY MONTH,
YEAR
ORDER BY YEAR,
MONTH) CLOSED
ON( OPEN.MONTH = CLOSED.MONTH
AND OPEN.YEAR = CLOSED.YEAR )

is there a way to break this down by owner?
My CTO would like a report that show total tickets for each ower and a total closed for that same tech. And of

kdasanmartino,

Is this the report your looking for?
SELECT OPEN.OWNER,
OPEN.MONTH,
OPEN.YEAR,
Coalesce(OPEN.OPEN, 0) AS OPENED,
Coalesce(CLOSED.CLOSED, 0) AS CLOSED
FROM (SELECT Coalesce(U.FULL_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.FULL_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
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,
MONTH,
OWNER

You may also want to take a look at this post. It shows number of tickets opened and closed by quarter.
http://itninja.com/question/number-of-tickets-open-and-closed-by-quarter

David;
Thanks!. this looks like what he's looking for.

I am having problems, when I run this report it only shows the closed tickets we have an intermediate step of Completed which is still open but needs to be treated as closed for reporting purposes. The Completed status is stalled if that helps. Any Help would be most appreciated.

Regards
Fraser Waugh