On Tuesday, November 14, 2017 at 2:55:30 AM UTC-8, Shad wrote:
>
> Hi,
>
> Please someone guide me with this?
>
> I have created a ticket statistics using MySQL query as follows:
>
>
> WITH
>> cutoff_time(n, usec) AS
>> (SELECT 0,1e6 * (strftime('%s', 'now'))
>> UNION ALL
>> SELECT n + 1, usec - (1e6 * 86400 * 7) FROM cutoff_time LIMIT 520),
>>
>> open_time(id, usec, version) AS
>> (SELECT id, time, version FROM ticket
>> WHERE version IS "1.0.0" AND type IS 'Bug'),
>>
>> close_time(id, usec) AS
>> (SELECT ticket, ticket_change.time
>> FROM ticket_change
>> LEFT JOIN ticket t ON ticket = t.id
>> WHERE field = 'status' AND newvalue IN ('closed', 'fixed',
>> 'invalid', 'duplicate', 'wontfix', 'closed(wontfix)', 'closed(invalid)')
>> AND version IS '1.0.0' AND type IS 'Bug'
>> GROUP BY ticket),
>>
>> num_closed(n, cnt) AS
>> (SELECT n, COUNT(id)
>> FROM cutoff_time
>> JOIN close_time ON cutoff_time.usec > close_time.usec
>> GROUP BY n),
>>
>> num_opened(n, cnt) AS
>> (SELECT n, COUNT(id)
>> FROM cutoff_time
>> JOIN open_time ON cutoff_time.usec > open_time.usec
>> GROUP BY n)
>>
>> SELECT
>> date(cutoff_time.usec / 1e6, 'unixepoch') AS 'Date',
>> num_opened.cnt - IFNULL(num_closed.cnt, 0) AS 'Active',
>> num_opened.cnt AS 'Opened',
>> IFNULL(num_closed.cnt, 0) AS 'Closed'
>> FROM cutoff_time
>> JOIN num_opened USING (n)
>> LEFT OUTER JOIN num_closed USING (n)
>> ORDER BY date
>>
>
> As we can see the logic is very simple, this query counts all the opened
> tickets and then subtracts the closed tickets, which gives me the number of
> active tickets.
>
> But now I found a problem with this code. That it does not count the
> reopened tickets, I don't understand how would I include even the reopened
> tickets to Active tickets number.
>
> Any help is much appreciated.
>
> Thanking you & regards,
> Shad
>
Your close time query is counting tickets that have been closed at least
once in the ticket history. A reopened ticket has been closed, but it's
current status is not closed. You need to order the ticket change events by
time and only count tickets in which the most recent change with
field='status' is in the list of values ('closed', 'fixed', ...).
However, on that last point the query looks a bit odd in that it's checking
if 'status' has values like 'invalid' and 'duplicate'. In a default Trac
installation, those are values of the "resolution" field, not the status
field. It's possible that you've modified your workflow to make those valid
ticket statuses, but I think it would be an odd configuration. You can
check with:
$trac-admin /path/to/env resolution list
Possible Values
---------------
duplicate
worksforme
fixed
invalid
wontfix
Looking at your workflow should also reveal if statues like 'invalid' are
really valid statuses, or are actually resolutions.
- Ryan
--
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 post to this group, send email to trac-users@googlegroups.com.
Visit this group at https://groups.google.com/group/trac-users.
For more options, visit https://groups.google.com/d/optout.
