Hi,

I'm after some help writing a report that will give my the breakdown of tickets opened and closed for each month.

this is the code i have so far, but need it for all tickets in the system:

SELECT * FROM

(SELECT YEAR(TIME_OPENED) as 'Year'
FROM
ORG1.HD_TICKET
WHERE
HD_TICKET.TIME_OPENED >= '2014-10-01 00:00' AND
HD_TICKET.TIME_OPENED <= '2014-10-31 23:59') d,

(SELECT monthname(TIME_OPENED) as 'Month'
FROM
ORG1.HD_TICKET
WHERE
HD_TICKET.SUBMITTER_ID <> '1366' AND
HD_TICKET.TIME_OPENED >= '2014-10-01 00:00' AND
HD_TICKET.TIME_OPENED <= '2014-10-31 23:59') c,

(SELECT COUNT(TIME_OPENED) as 'Month Opened'
FROM
ORG1.HD_TICKET
WHERE
HD_TICKET.SUBMITTER_ID <> '1366' AND
HD_TICKET.TIME_OPENED >= '2014-10-01 00:00' AND
HD_TICKET.TIME_OPENED <= '2014-10-31 23:59') a,

(SELECT COUNT(TIME_CLOSED) as 'Total Closed'
FROM
ORG1.HD_TICKET
WHERE
HD_TICKET.SUBMITTER_ID <> '1366' AND
HD_TICKET.TIME_CLOSED >= '2014-10-01 00:00' AND
HD_TICKET.TIME_CLOSED <= '2014-10-31 23:59') b 

Limit 1

The submitter ID is to remove automated tickets submitted by a few servers.

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1
Did you mean such a report ?? I think it is a quick solution but it works.

select *
  from (select YEAR(TIME_OPENED) as year, MONTH(TIME_OPENED) as month, count(*) as opened, 0 as closed
          from HD_TICKET
         group by YEAR(TIME_OPENED), MONTH(TIME_OPENED)
        union
        select YEAR(TIME_CLOSED) as year, MONTH(TIME_CLOSED) as month, 0, count(*)
          from HD_TICKET
         group by YEAR(TIME_CLOSED), MONTH(TIME_CLOSED)) as sel
where year <> 0 and month <> 0
group by 1,2
order by 1,2
Answered 04/10/2015 by: aragorn.2003
Red Belt

  • This sort of works! I'm getting 0 in all rows for closed tickets but the opened is working fine. do i need to change something on the code?
  • So i would like to see ¦ Year ¦ Month ¦ Total Opened ¦ Total Closed ¦
Please log in to comment
1
Here's a query I came up with:

SELECT YEAR(CREATED) AS TicketYears, MONTH(CREATED) AS TicketMonths, COUNT(ID) AS "Opened",
(SELECT COUNT(ID) FROM HD_TICKET WHERE YEAR(TIME_CLOSED) = TicketYears and MONTH(TIME_CLOSED) = TicketMonths) AS "Closed"
FROM ORG1.HD_TICKET
GROUP BY YEAR(CREATED), MONTH(CREATED)

Answered 04/13/2015 by: chucksteel
Red Belt

  • cheers, this one worked great, thank you for your help.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share