1

In a third-party application (RT4) running on MySQL 5.5 I have a series of helpdesk tickets represented as records in a table. Each record has a date/time created, a date/time resolved, and a merge flag (NULL or 1).

id int(11) AI PK, Created datetime, Resolved datetime, IsMerged smallint(6)

When the ticket is created the created field is populated and the resolved field and merge flag are NULL. When the ticket is closed the resolved field is populated. If the ticket is a duplicate the merge flag is set to 1, and the resolved date is set to 1970-01-01 00:00:00.

I've looked at a number of the offered related answers and of those mysql: counting number of tickets which are open per day basis seemed the most promising. I feel it's almost what I need but I can't extrapolate from the answers there to my use case.

I can list the number of tickets created per year easily enough -

select year(Created) as Created_Yr, count(*) as N_Tickets from rt4.Tickets
group by year(Created)
order by 1

A similar query can list the set of non-merged tickets per year -

select year(Resolved) as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where Resolved is not null
group by year(Resolved)
order by 1

However, what I would like is to list the number of tickets resolved or merged, per year.

I can do this year by year like this -

select 2016 as Resolved_Yr, count(*) as N_Tickets from rt4.Tickets
where ( Resolved >= '2016-01-01' and Resolved < '2017-01-01' )
or ( Created >= '2016-01-01' and Created < '2017-01-01' and IsMerged = 1 )

We have data from years 2012 to 2017 and I'm hoping to avoid having to run a query per year and manually collate the results.

Is there a query that will list the Resolved year (or the Created year if the ticket was merged), for all years present in the database?

2
SELECT * 
  FROM ( SELECT DISTINCT year(Created) AS Created
           FROM rt4.Tickets
       ) AS w

  LEFT JOIN ( SELECT year(Resolved) AS Resolved
                   , count(*) AS resolved_cnt
                FROM rt4.Tickets
               WHERE Resolved IS NOT NULL
               GROUP BY year(Resolved) 
            ) AS z
         ON z.Resolved = w.Created

  LEFT JOIN ( SELECT year(Created) AS Created
                   , count(*) AS merged_cnt
                FROM rt4.Tickets
               WHERE Merged IS NOT NULL
               GROUP BY year(Created) 
            ) AS q
         ON q.Created = w.Created
;

Sure you have to remember that functional restrictions like year() in the WHERE, JOIN...ON or GROUP BY clauses cannot be speeded up by indexes and can be VERY slow.

I hope that query wouldn't be runned on the regular basis.

| improve this answer | |
  • This is fantastic thank you. Just FTR I've used SELECT w.Created, w.created_cnt, z.resolved_cnt + ifnull(q.merged_cnt,0) as N_Resolved_Merged, and changed the SELECT DISTINCT ... to a SELECT year(Created), count(*) ... GROUP BY. – roaima Jul 18 '17 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.