This would give you all closed tickets in the last 1 month:
____________________________________________
SELECT DISTINCT
id AS ticket, time, owner, version, milestone, resolution, status
FROM ticket t
WHERE status IN ('closed')
AND time >= (select strftime ("%s", "now", "-1 month"))
ORDER BY time
____________________________________________
or if you wanted all in the current calendar month you could change
the select
line to:
AND time >= (select strftime ("%s", "now", "start of month"))
Another report I have is for selecting defects and enhancement tickets
which
were closed between two dates:
___________________________________________
select id, type, summary, component, milestone, owner,
datetime((select max(tc.time) from ticket_change tc
where t.id = tc.ticket and field = 'status' and newvalue =
'closed'),
'unixepoch', 'localtime') as closedate,
(select max(tc.time) from ticket_change tc
where t.id = tc.ticket and field = 'status' and newvalue =
'closed')
as closetime
from ticket t
where status = 'closed'
and closetime > strftime('%s',datetime('2006-03-24 16:38:45')) +0
and closetime < strftime('%s',datetime('2006-04-06 15:25:34')) +0
AND type IN ('defect','enhancement')
order by -closetime;
___________________________________________
On Nov 28, 5:30 pm, [EMAIL PROTECTED] wrote:
> Folks,
>
> I'm a bit new to the TRACS reporting and my SQL skills are non-
> existent. However I need to generate reports from tickets we put into
> TRACS.
>
> I want to create a report that will only give me closed tickets for a
> specified date range (typically, 1 month like you can do in the
> timeline view). It would also be nice (maybe as a seperate report) to
> collate closed tickets by component and a specified date range.
>
> Is this possible? Does anyone know how to do it?
>
> thanks
>
> Pat
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
