QueryResolvedByUser
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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.