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