Dears,

I want to generate a report that will gives me only numbers about the tickets in certain queue in a certain period (e.x previous month). 
When I created the report through the wizard and didn't select any field to retrieve, the report include empty lines by line numbers!

I just want statistics like the below table:

OPENED TICK#
PENDING TICK#
410
8


So, I edited the SQL Code, and selected only the count for the opened tickets and it works with. But how can I include also the  PENDING TICK# (not closed) in the same report ? It's too much to create 2 reports for the same purpose.

This is the SQL Code that will retrieve the OPENED TICK#:

SELECT COUNT(HD_TICKET.ID) as "Opened Tickets Total in Bentley Support Queue Last Month"

FROM HD_TICKET

where HD_TICKET.HD_QUEUE_ID = 7 AND (((  date(HD_TICKET.TIME_OPENED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 1  month)  and date(HD_TICKET.TIME_OPENED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) ))  ORDER BY TIME_OPENED


And this is the SQL Code that will retrieve the PENDING TICK#:

SELECT COUNT(HD_TICKET.ID) as "Pending Tickets Total in Bentley Support Queue Last Month"

FROM HD_TICKET  LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) 

WHERE HD_TICKET.HD_QUEUE_ID = 7 AND (((  date(HD_TICKET.TIME_OPENED) >= date_sub(date_sub(curdate(), interval dayofmonth(curdate())-1 day), interval 1  month)  and date(HD_TICKET.TIME_OPENED) < date_sub(curdate(), interval dayofmonth(curdate())-1 day) ) ) AND ((HD_STATUS.NAME != 'Closed')))  

How can I merge them into 1 report ?
 
Please advise..
Thanks in advance.
3 Comments   [ + ] Show Comments

Comments

  • Just to be clear, do you want a report showing how many tickets were opened in the previous month and how many are still open? The two queries you posted don't include any statements that differentiate between a ticket being opened and closed.
  • I have a similar query that reports the number of open and number of tickets on hold. Here is what my query looks like:-

    SELECT
    (SELECT Count(HD_STATUS.NAME) AS STATUS FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 8) AND ((date(HD_TICKET.TIME_CLOSED) = curdate() ) AND (HD_STATUS.NAME not like '%closed%')) ORDER BY STATUS) AS OPEN,

    (SELECT Count(HD_STATUS.NAME) AS STATUS FROM HD_TICKET JOIN HD_STATUS ON (HD_STATUS.ID = HD_TICKET.HD_STATUS_ID) WHERE (HD_TICKET.HD_QUEUE_ID = 8) AND ((date(HD_TICKET.TIME_CLOSED) = curdate() ) AND (HD_STATUS.NAME = 'On Hold')) ORDER BY STATUS) AS HOLD

    Just substitute the 'On Hold' for Pending and Queue number
  • Thank you guys! Really appreciate your help.
    It has been solved.

    Thanks again!
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity