/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Report code for total raised/total closed each month

04/10/2015 1503 views
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:
0 Comments   [ + ] Show comments

Comments


All 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 ¦
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.

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ