On Fri, Jun 7, 2024 at 7:12 PM Clemens Feige <[email protected]> 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 <[email protected]> (大前 潤)
--
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/trac-users/CAEVLMaj%2BozNFYpzHc-M34KPngxtUBQcSiQ20wZe5LtJKa7V3Tw%40mail.gmail.com.
