Following command works from SQL, but it consists of two queries and
is not accepted by TRAC:
The first one creates a temporary VIew,
the second one groups the view
CREATE TEMP VIEW opened_closed as
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;
SELECT dt,sum(new),sum(closed) from opened_closed group by dt;
So my current solution is to do following on the trac host from a
sqlite3 prompt:
I create a permanent view:
CREATE VIEW opened_closed as
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;
then I can use below query from within trac:
SELECT dt,sum(new),sum(closed) from opened_closed group by dt;
Not too elegant, but it seems to do the job.
bye
Klaus
On Mar 5, 9:15 am, klausfpga <[email protected]> wrote:
> 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.
