query for all tickets closed in a time span

[Clemens Feige]

Hello

How can I query i.e. create a ticket report of all tickets which have
been closed in a certain time span e.g. in a particular year?

I want to know this for annual statistics. Each year a couple of tickets
are closed (in that particular year) and I want to know how many. Please
not that I am not asking for the total accumulated number of closed
tickets at a certain moment.

It is easy to use the query for closed tickets and for the last
modification date. But the last modification date is not necessarily the
closure date.

One probably needs to query (with SQL?) for ticket changes in the
desired time span where the ticket status changed to "closed".

Does anybody have a better idea?
Or maybe a ready to use SQL fragment?

Thanks
Clemens

[Jun Omae]

To retrieve the time when a ticket was closed, is able to use the
"ticket_change" table like the following:

====
SELECT
t.id,
(
CASE t.status
WHEN 'closed'
THEN (
SELECT tc.time
FROM ticket_change AS tc
WHERE tc.ticket=t.id AND field='status' AND newvalue='closed'
ORDER BY tc.time DESC LIMIT 1)
ELSE NULL
END
) AS closed_at
FROM ticket AS t
====

--
Jun Omae <jun...@gmail.com> (大前 潤)