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
--
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.
