-----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.
