Customized Report in Dell KACE - Service Desk -
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
Answers (0)
Please log in to answer
Be the first to answer this question
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 - Druis 10 years ago
It has been solved.
Thanks again! - salelyani 10 years ago