Report code for total raised/total closed each month
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.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
aragorn.2003
9 years ago
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
Comments:
-
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? - chris.poston 9 years ago
-
So i would like to see ¦ Year ¦ Month ¦ Total Opened ¦ Total Closed ¦ - chris.poston 9 years ago
Posted by:
chucksteel
9 years ago
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)
Comments:
-
cheers, this one worked great, thank you for your help. - chris.poston 9 years ago
