Thank you for your reply Ryan, but I am new user to TRAC and have no idea
like to do it and implement it with respect to this plugin.
Indeed, I tried modifying the SQL report as follows:
WITH
cutoff_time(n, usec) AS
(SELECT 0, 1e6 * (strftime('%s', 'now') -
((strftime('%s', 'now') + (86400 * 3)) % (86400 * 7)))
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 "2.0"),
close_time(id, usec, version) AS
(SELECT ticket, MAX(time), version
FROM ticket_change
WHERE field = 'status' AND newvalue = 'closed' AND version = '2.0'
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
But I found an error "there is no such column version" , I understand that
this error is because there is no column for version in "ticket_change". So
now, I would like to add a condition to select the version for the
closed_time function, how do I do it? Please help?
Thank you in advance.
On Monday, October 2, 2017 at 6:23:22 PM UTC+2, RjOllos wrote:
>
>
>
> On Monday, October 2, 2017 at 9:09:17 AM UTC-7, Shad wrote:
>>
>> Hi,
>>
>> I am a new user to TRAC and SQL knowledge is NULL. I wanted to create a
>> report statistics for opened, closed and Active tickets based on ticket
>> version.
>>
>> For a general global statistics of tickets, I followed this link:
>> https://trac-hacks.org/wiki/DygraphsVisualizationPlugin
>>
>> But now I would like to have a specific report for a specific version
>> (for example version: 2.0)
>>
>> How do I do it? Any help is much appreciated.
>>
>> Regards,
>> Shad
>>
>
> I'm not familiar with that plugin. Can it use a saved custom query rather
> than an SQL report? It is simple to make a saved custom query for a
> specific version.
> https://trac.edgewall.org/wiki/TracQuery
>
> - 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.
