[Trac] Re: Count Incoming tickets by milestone, and count closed tickets by milestone and user.
On Jun 4, 4:21 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 3, 4:55 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 3, 3:34 pm, yoheeb yoh...@gmail.com wrote: On Jun 3, 2:10 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 2, 3:54 pm, Lukasz Szybalski szybal...@gmail.com wrote: Hello, I'm trying to get management reports for the following: 1. Count Incoming tickets by milestone sort by date, (30 new tickets for milestone on 06-02-09) 2. Count closed tickets by milestone and user. (25 tickets closed by xyz on 6-2-09)(list a count by user in each milestone) 3. lookup by custom field? (custom field = account_number) (With query I can do that, but how do I put it on a wiki so that they can type in account# and press query or something along these lines.) Where can I find examples on how to do this? snip how can I convert t.time to a date, so that I can group by t,time as a date and not as a datetime?? Right now this query groups by datetime object and I need it to order by date only. SELECT time as created,time,milestone, count(*) AS 'Count of Tickets' FROM ticket WHERE status IN ('closed') AND time strftime('%s',datetime (now,-14 days))+0 GROUP BY (milestone IS NULL), milestone, created How to convert time into date like 20090604??? Thanks, Lucas and the second query select user, milestone, count(*) , from ?? where status = fixed group by user, milestone, closeddate An example of some queries that select things by date/time can be found in this thread: http://groups.google.com/group/trac-users/browse_thread/thread/83c426562091c253/d1da6fdc7e5eac84?hl=enlnk=gstq=between+dates#d1da6fdc7e5eac84 what you are after is the 'unixepoch', 'localtime') as closedate partI think assuming sqllite. If you are on postGres, I can't help you, my SQL is weak!, and I have never used postGres, so I don't know the variance. The key in that thread that might be useful in your case, they actually test for a transition to a specific state. In your case, you probably only need the creation date if date today, or today -14. The html form for submission, on the other hand, would be cool to know. I am still of the mind that the best web browser to use in lynx, and that if I bury my head long enough, the whole idea of web forms etc will just pass me by :D Now to go figure out this new fandangled mouse thingy :wq --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Trac Users group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~--~~~~--~~--~--~---
[Trac] Re: Count Incoming tickets by milestone, and count closed tickets by milestone and user.
On Jun 3, 4:55 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 3, 3:34 pm, yoheeb yoh...@gmail.com wrote: On Jun 3, 2:10 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 2, 3:54 pm, Lukasz Szybalski szybal...@gmail.com wrote: Hello, I'm trying to get management reports for the following: 1. Count Incoming tickets by milestone sort by date, (30 new tickets for milestone on 06-02-09) 2. Count closed tickets by milestone and user. (25 tickets closed by xyz on 6-2-09)(list a count by user in each milestone) 3. lookup by custom field? (custom field = account_number) (With query I can do that, but how do I put it on a wiki so that they can type in account# and press query or something along these lines.) Where can I find examples on how to do this? I've noticed on the website trac seem to want to replace the reports with query. Would anybody have a query syntax for all the default reports that I can paste into wiki page? This for example allows me to modify the new tickets by milestone: [query:status=new|assigned|reopenedversion=1.0 Active tickets against 1.0] But with the other reports most of them ask for action, and/or count on particular category or action. How many tickets we had, how many closed, who closed most of them, how many are outstanding, who is doing most of the work,etc? Anybody knows how can I display: user , ticket count in a table? The only thing I was able to do is on a wiki page do: Users Assigned Tickets: 1. admin : [[TicketQuery(status=assignedowner=admin, count)]] But this way I need to list every user manually. Is there a way to display all users? user1, 5 user2, 55 user3, 10 .. I can also link to custom query if that is easier... 2. How can I query by date? All new tickets today by milestone? (this would include new, accepted, fixed,..etc)? Ideas? Thanks, Lucas Couple options actually. The Opteros folks have some plugins for their OForge project that does some of this. a sql report can be created to do this. (there is a created date or similiar field you could compare to the current date/time) there is a plugin to display an sql query in a table I would probably try the sql version. I've noticed there is a button that saves you custom query which says: Query for Report: (can be either SQL or, if starting with query:, a TracQuery expression) Is SQL supported out of box? What would be a sample sql syntax that I could use with: Trac: 0.11.1 What would the sql statement look like for the custom query, something that says: select milestone, count(*) , from ?? group by milestone, created date. how can I convert t.time to a date, so that I can group by t,time as a date and not as a datetime?? Right now this query groups by datetime object and I need it to order by date only. SELECT time as created,time,milestone, count(*) AS 'Count of Tickets' FROM ticket WHERE status IN ('closed') AND time strftime('%s',datetime (now,-14 days))+0 GROUP BY (milestone IS NULL), milestone, created How to convert time into date like 20090604??? Thanks, Lucas and the second query select user, milestone, count(*) , from ?? where status = fixed group by user, milestone, closeddate --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Trac Users group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~--~~~~--~~--~--~---
[Trac] Re: Count Incoming tickets by milestone, and count closed tickets by milestone and user.
On Jun 2, 3:54 pm, Lukasz Szybalski szybal...@gmail.com wrote: Hello, I'm trying to get management reports for the following: 1. Count Incoming tickets by milestone sort by date, (30 new tickets for milestone on 06-02-09) 2. Count closed tickets by milestone and user. (25 tickets closed by xyz on 6-2-09)(list a count by user in each milestone) 3. lookup by custom field? (custom field = account_number) (With query I can do that, but how do I put it on a wiki so that they can type in account# and press query or something along these lines.) Where can I find examples on how to do this? I've noticed on the website trac seem to want to replace the reports with query. Would anybody have a query syntax for all the default reports that I can paste into wiki page? This for example allows me to modify the new tickets by milestone: [query:status=new|assigned|reopenedversion=1.0 Active tickets against 1.0] But with the other reports most of them ask for action, and/or count on particular category or action. How many tickets we had, how many closed, who closed most of them, how many are outstanding, who is doing most of the work,etc? Anybody knows how can I display: user , ticket count in a table? The only thing I was able to do is on a wiki page do: Users Assigned Tickets: 1. admin : [[TicketQuery(status=assignedowner=admin, count)]] But this way I need to list every user manually. Is there a way to display all users? user1, 5 user2, 55 user3, 10 .. I can also link to custom query if that is easier... 2. How can I query by date? All new tickets today by milestone? (this would include new, accepted, fixed,..etc)? Ideas? Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Trac Users group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~--~~~~--~~--~--~---
[Trac] Re: Count Incoming tickets by milestone, and count closed tickets by milestone and user.
On Jun 3, 2:10 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 2, 3:54 pm, Lukasz Szybalski szybal...@gmail.com wrote: Hello, I'm trying to get management reports for the following: 1. Count Incoming tickets by milestone sort by date, (30 new tickets for milestone on 06-02-09) 2. Count closed tickets by milestone and user. (25 tickets closed by xyz on 6-2-09)(list a count by user in each milestone) 3. lookup by custom field? (custom field = account_number) (With query I can do that, but how do I put it on a wiki so that they can type in account# and press query or something along these lines.) Where can I find examples on how to do this? I've noticed on the website trac seem to want to replace the reports with query. Would anybody have a query syntax for all the default reports that I can paste into wiki page? This for example allows me to modify the new tickets by milestone: [query:status=new|assigned|reopenedversion=1.0 Active tickets against 1.0] But with the other reports most of them ask for action, and/or count on particular category or action. How many tickets we had, how many closed, who closed most of them, how many are outstanding, who is doing most of the work,etc? Anybody knows how can I display: user , ticket count in a table? The only thing I was able to do is on a wiki page do: Users Assigned Tickets: 1. admin : [[TicketQuery(status=assignedowner=admin, count)]] But this way I need to list every user manually. Is there a way to display all users? user1, 5 user2, 55 user3, 10 .. I can also link to custom query if that is easier... 2. How can I query by date? All new tickets today by milestone? (this would include new, accepted, fixed,..etc)? Ideas? Thanks, Lucas Couple options actually. The Opteros folks have some plugins for their OForge project that does some of this. a sql report can be created to do this. (there is a created date or similiar field you could compare to the current date/time) there is a plugin to display an sql query in a table and probably others. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Trac Users group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~--~~~~--~~--~--~---
[Trac] Re: Count Incoming tickets by milestone, and count closed tickets by milestone and user.
On Jun 3, 3:34 pm, yoheeb yoh...@gmail.com wrote: On Jun 3, 2:10 pm, Lukasz Szybalski szybal...@gmail.com wrote: On Jun 2, 3:54 pm, Lukasz Szybalski szybal...@gmail.com wrote: Hello, I'm trying to get management reports for the following: 1. Count Incoming tickets by milestone sort by date, (30 new tickets for milestone on 06-02-09) 2. Count closed tickets by milestone and user. (25 tickets closed by xyz on 6-2-09)(list a count by user in each milestone) 3. lookup by custom field? (custom field = account_number) (With query I can do that, but how do I put it on a wiki so that they can type in account# and press query or something along these lines.) Where can I find examples on how to do this? I've noticed on the website trac seem to want to replace the reports with query. Would anybody have a query syntax for all the default reports that I can paste into wiki page? This for example allows me to modify the new tickets by milestone: [query:status=new|assigned|reopenedversion=1.0 Active tickets against 1.0] But with the other reports most of them ask for action, and/or count on particular category or action. How many tickets we had, how many closed, who closed most of them, how many are outstanding, who is doing most of the work,etc? Anybody knows how can I display: user , ticket count in a table? The only thing I was able to do is on a wiki page do: Users Assigned Tickets: 1. admin : [[TicketQuery(status=assignedowner=admin, count)]] But this way I need to list every user manually. Is there a way to display all users? user1, 5 user2, 55 user3, 10 .. I can also link to custom query if that is easier... 2. How can I query by date? All new tickets today by milestone? (this would include new, accepted, fixed,..etc)? Ideas? Thanks, Lucas Couple options actually. The Opteros folks have some plugins for their OForge project that does some of this. a sql report can be created to do this. (there is a created date or similiar field you could compare to the current date/time) there is a plugin to display an sql query in a table I would probably try the sql version. I've noticed there is a button that saves you custom query which says: Query for Report: (can be either SQL or, if starting with query:, a TracQuery expression) Is SQL supported out of box? What would be a sample sql syntax that I could use with: Trac: 0.11.1 What would the sql statement look like for the custom query, something that says: select milestone, count(*) , from ?? group by milestone, created date. and the second query select user, milestone, count(*) , from ?? where status = fixed group by user, milestone, closeddate Sample syntax? What is the table name? What columns have create date, and fixed date? Thanks, Lucas --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups Trac Users group. To post to this group, send email to trac-users@googlegroups.com To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~--~~~~--~~--~--~---