HiCM,
Thanks for your answer.
Do you use mysql or sqlite?
The query doesn't seem to work on sqlite.
I get an error message:
> SQL error: near "(": syntax error
I can create following query to list all opened and closed tickets by
date, however I'd like to have the SUM() of opened and closed tickets
per day (just one line per day).
SELECT
id,
date(t.time,'unixepoch') as dt,
1 as new,
0 as closed
FROM ticket as t
UNION ALL
SELECT
ticket as id,
date(c.time,'unixepoch') as dt,
0 as new,
1 as closed
FROM ticket_change as c
WHERE c.field = 'status' and c.newvalue = 'closed'
ORDER by dt;
I could do some kind of cheating and create a VIEW in sqlite with
above query.
Then I would probably be able to group / sum the result of above
query.
This means however I could do this only at hosts where I have access
to the sqlite data base (for creating the view)
On Mar 5, 6:29 am, CM Lubinski <[email protected]> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> You can also use a union to combine the results of two or more sub
> queries. We use this to end several of our reports with a summation of
> the data.
>
> For example, here's a report which shows all open and closed tickets, by
> group, with a summation for each. You'll see one suquery to get all open
> tickets, one to summarize those open tickets into "X open tickets",
> another to get all closed tickets, and a final to summarize those
> tickets. This example could be more efficient by combining the 'Open'
> group and the 'Closed' group, but I hope this gets the point across.
>
> SELECT * FROM
> (SELECT 'Open' AS __group__,
> id,
> summary
> FROM ticket WHERE status = 'open') AS subquery
> UNION
> (SELECT 'Open Total' AS __group__,
> null,
> count(*) || ' open tickets'
> FROM ticket WHERE status = 'open'
> )
> UNION
> (SELECT 'Closed' AS __group__,
> id,
> summary
> FROM ticket WHERE status = 'closed')
> UNION
> (SELECT 'Closed Total' AS __group__,
> null,
> count(*) || ' closed tickets'
> FROM ticket WHERE status = 'closed'
> )
> ORDER by __group__
>
> Best of Luck,
> CM Lubinski
>
> http://cmlubinski.info
>
>
>
> Chris Nelson wrote:
> > 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
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFLkJasfzi1OiZiJLARAgh0AKCAmbgck0shNYC8Q49rOMVp0zIbngCeLMEY
> LFX6I04wUH9pgVL1sr3WeDo=
> =08Gp
> -----END PGP SIGNATURE-----
--
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.
