On Fri, Jun 7, 2024 at 7:12 PM Clemens Feige <c.fe...@osypkamed.com> wrote:
>
> 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
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 <jun6...@gmail.com> (大前 潤)
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to trac-users+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/trac-users/CAEVLMaj%2BozNFYpzHc-M34KPngxtUBQcSiQ20wZe5LtJKa7V3Tw%40mail.gmail.com.
