Admin: 2 revisions imported

2016-04-06T20:20:30Z

2 revisions imported

New page

Show the amount of tickets resolved by users.

-- Tickets resolved per user
SELECT t.count, u.name, u.realname FROM
(SELECT creator, COUNT(creator) AS count
FROM transactions
WHERE objecttype = 'RT::Ticket'
AND type = 'Status'
AND newvalue = 'resolved'
GROUP BY creator) AS t
LEFT JOIN users u ON t.creator = u.id;

----

Changed as table names are beginning with capital letters.

SELECT t.count, u.name, u.realname FROM
(SELECT creator, COUNT(creator) AS count
FROM Transactions
WHERE objecttype = 'RT::Ticket'
AND type = 'Status'
AND newvalue = 'resolved'
GROUP BY creator) AS t
LEFT JOIN Users u ON t.creator = u.id;

----

-- NB: Will not work as writen on mySQL. Ended up having to do following:

CREATE TEMPORARY TABLE t SELECT Creator, COUNT(Creator) as count;
SELECT t.count, u.name, u.realname FROM t LEFT JOIN Users u ON t.Creator = u.id;

Alternate PHP method would be to store results into arrays and assemble at presentation layer.

- [[MichaelErana]], CTO PC Network Inc.

----

Update: The above (original without using temporary table) works on [[MySQL]] 5.0.18.

- Bill R. Williams, ETSU Library Systems

----

--Query as above but including date range limit and total time worked (August 2, 2005)

CREATE TEMPORARY TABLE t SELECT Owner, COUNT(Owner) as count, SUM(TimeWorked)/60 AS TotalTime
From Tickets
where Resolved>'2005-07-01'
AND Resolved<'2005-07-31'
GROUP By Owner;
SELECT t.count, u.realname, t.TotalTime
FROM t
LEFT JOIN Users u ON t.Owner = u.id
GROUP by t.Owner
ORDER by COUNT Desc;

NB: If you run this more than once in a session, you'll need to drop the temp table before the next run.

DROP TEMPORARY TABLE t;

- [[MichaelErana]], CTO PC Network Inc.

QueryResolvedByUser - Revision history

Admin: 2 revisions imported