Does anyone have a report that shows total number of tickets open and total closed per month? I really appreciate any help!

Thanks!

Chris
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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 )
Answered 11/19/2010 by: dchristian
Red Belt

Please log in to comment
0
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
Answered 11/21/2010 by: kdasanmartino
Second Degree Green Belt

Please log in to comment
0
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
Answered 11/22/2010 by: dchristian
Red Belt

Please log in to comment
0
David;
Thanks!. this looks like what he's looking for.
Answered 11/22/2010 by: kdasanmartino
Second Degree Green Belt

Please log in to comment
0
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
Answered 12/16/2010 by: fwaugh
Yellow Belt

Please log in to comment
0
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.
Answered 12/17/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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
Answered 12/20/2010 by: laltes
Senior Yellow Belt

Please log in to comment
0
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
Answered 12/20/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
Exactly what I need

Thanks
Lyle
Answered 12/20/2010 by: laltes
Senior Yellow Belt

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

Thanks!
Answered 06/22/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
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]
Answered 06/30/2011 by: dchristian
Red Belt

Please log in to comment
0
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
Answered 06/30/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
You right!

I corrected the statement, please try again.
Answered 06/30/2011 by: dchristian
Red Belt

Please log in to comment
0
That word dchristian. Thanks for your help!
Answered 07/07/2011 by: nslatter
Senior Yellow Belt

Please log in to comment
0
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.
Answered 08/25/2011 by: davids
Senior Yellow Belt

Please log in to comment
0

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


Answered 06/24/2016 by: dcavazos
White Belt

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