Specify Date Range for Reports

[guthr...@yahoo.com]

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

[Erik Andersson]

Hi

 

You can do something like:

 

SELECT t.id,t.summary,t.component,t.milestone FROM ticket AS t,ticket_change AS tc
WHERE t.id=tc.ticket
AND field='status'
AND newvalue='closed'
AND strftime('%Y-%m-%d',datetime( tc.time, 'unixepoch'),'localtime') >= datetime('$FROM','localtime')
AND strftime('%Y-%m-%d',datetime(tc.time, 'unixepoch'),'localtime') < datetime('$TO','localtime')

 

Then pass the arguments TO and FROM by adding ?FROM=2007-10-01&TO=2007-11-01 to the end of the report url.

 

Also see: http://trac.edgewall.org/wiki/TracReports

 

Cheers / Erik
 

[the_J0ker]

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;
___________________________________________

[Erik Andersson]

If you use sqlite, this can be helpful: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Cheers / Erik

[Dirk]

Hi All,

I have a similar problem, but I would like to use the Query module, as
we've moved away from Reports. Is there any way to get something
comparable out of or hacked into the queries?

Thanks

Dirk