Thank you for your reply Ryan.
On Wednesday, November 15, 2017 at 12:20:22 AM UTC+1, RjOllos wrote:
>
>
>
> 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', ...).
>
I tried ordering by time as follows:
> 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 = 'closed' AND version IS '3.0.0
> Alpha' AND type IS 'Bug'
> ORDER BY ticket_change.time),
>
But it didn't work. I have no knowledge of SQLite either, so please guide
me in correcting this query.
Regards,
Shad
>
> 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.
