On Jun 4, 4:21 pm, Lukasz Szybalski <[email protected]> wrote: > On Jun 3, 4:55 pm, Lukasz Szybalski <[email protected]> wrote: > > > > > On Jun 3, 3:34 pm, yoheeb <[email protected]> wrote: > > > > On Jun 3, 2:10 pm, Lukasz Szybalski <[email protected]> wrote: > > > > > On Jun 2, 3:54 pm, Lukasz Szybalski <[email protected]> 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=en&lnk=gst&q=between+dates#d1da6fdc7e5eac84 what you are after is the 'unixepoch', 'localtime') as closedate part....I 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 [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 -~----------~----~----~----~------~----~------~--~---
