Re: [Trac] query for all tickets closed in a time span

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