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 (
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
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?