klausfpga wrote:
>> I wanted create a table, which reports for each day the number of
>> new, closed (and modified) tickets.
> ...
Ironically, I'm working on much the same report. What I want is all
tickets opened or closed in the past week. The code below shows
estimated and actual time from the T&E plugin and is based on an 0.11.6
system with the microsecond patch installed. Remove the "*hours" fields
and the /1000000 to use generically. Hope this helps. (And I'd be
happy for feedback on this query if I've made it harder than it is.)
----------------8<------------->8---------------------
SELECT
(CASE WHEN t.status="closed" THEN "Closed" ELSE "New" END) AS
__group__,
id AS ticket,
milestone,
summary,
owner,
resolution,
changetime AS _changetime,
t.description AS _description,
CAST(est.value AS REAL) as 'Est',
CAST(act.value AS REAL) as 'Act',
(CASE WHEN t.status="closed"
THEN STRFTIME('%m/%d/%Y', MAX(tc.time)/1000000, 'unixepoch')
ELSE STRFTIME('%m/%d/%Y', t.time/1000000, 'unixepoch') END) AS
changedate
FROM
ticket t, enum p
JOIN milestone on milestone.name = t.milestone
JOIN ticket_change tc ON tc.ticket = t.id
LEFT OUTER JOIN ticket_custom est ON
(t.id=est.ticket AND est.name='estimatedhours')
LEFT OUTER JOIN ticket_custom act ON
(t.id=act.ticket AND act.name='totalhours')
WHERE
(t.status = 'closed'
AND tc.field = 'status'
AND tc.oldvalue <> 'closed'
AND tc.newvalue = 'closed'
AND tc.time/1000000 >= (strftime('%s', 'now') - 7 * 86400))
OR (t.time/1000000 >= (strftime('%s', 'now') - 7 * 86400))
GROUP BY t.id
ORDER BY __group__,milestone, changedate
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
