<https://lh3.googleusercontent.com/-ldK266UtSBs/WfcRZzvZPNI/AAAAAAAAAnQ/O5rxyp0n_TYXWHg2zBmdZeQseqIa5JU2QCLcBGAs/s1600/Capture.PNG>
Hi,
I wanted to create a report statistics based on version, thus I used this
code to do the following:
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),
custom_time(id, usec, version, priority) AS
(SELECT id, time, version, priority FROM ticket
WHERE version IS "1.0" AND priority IN ("major", "Critical") AND
status NOT IN ("closed", "invalid", "fixed", "duplicate", "wontfix")
GROUP BY id),
open_time(id, usec,version) AS
(SELECT id, time, version FROM ticket
WHERE version IS "1.0"),
close_time(id, usec, version) AS
(SELECT id, MAX(time), version
FROM ticket
WHERE status IN ('closed', 'fixed', 'invalid', 'duplicate', 'wontfix',
'closed(wontfix)', 'closed(invalid)') AND version IS '1.0'
GROUP BY id),
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),
num_custom(n, cnt) AS
(SELECT n, COUNT(id)
FROM cutoff_time
JOIN custom_time ON cutoff_time.usec > custom_time.usec
GROUP BY n)
SELECT
date(cutoff_time.usec / 1e6, 'unixepoch') AS 'Date',
num_custom.cnt AS 'Critical_&_Major_Tickets',
num_opened.cnt - IFNULL(num_closed.cnt, 0) AS 'Active',
num_opened.cnt AS 'ALL_Tickets',
IFNULL(num_closed.cnt, 0) AS 'Closed'
FROM cutoff_time
JOIN num_custom USING (n)
LEFT OUTER JOIN num_opened USING (n)
LEFT OUTER JOIN num_closed USING (n)
ORDER BY date
But using this I cannot group the tickets properly. I mean the tickets are
merging irrespective of time. For example if I create a ticket on week 1
and close it in week 2 , it is been counted for week 1 and week 2 i.e, the
active tickets number for week 1 which should be 1 is changing to zero even
if it is closed during week 2.
In the attached image one can see that the 2 tickets were opened on
10/07/2017 and was closed on 30/10/2017 but the closed tickets are also
being counted for other days from 10/07/2017 to 30/10/2017
Please guide me in this regard, I am sure that there is a problem of
grouping.
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.
