Hi Landon, below is some sql I wrote (I'm not too good at sql so some of it could probably be optimized). It pulls a bunch of fields both system and custom as well as the first comment on the ticket and names the columns something friendly. Also it compensates for timezones since the db is in UTC.
Hopefully it helps. The custom fields I am pulling are "Priority" (not the built-in one) and "Category". I have two different "Category" custom fields from two different queues but I am showing them both in one column, hence the OR in the first custom field join statement below. Let me know --begin SQL-- SELECT T.id, T.EffectiveId, DATE(CONVERT_TZ(T.Created, '+00:00', '-04:00')) as 'Day Created', CONVERT_TZ(T.Created, '+00:00', '-04:00') as Created, CONVERT_TZ(T.Resolved, '+00:00', '-04:00') as Resolved, WEEK(CONVERT_TZ(T.Created, '+00:00', '-04:00')) as week, MONTH(CONVERT_TZ(T.Created, '+00:00', '-04:00')) as Month, U3.Name as 'Requestor', U6.EmailAddress as 'Requestor Email', U5.RealName as 'Requestor RealName', U4.City as 'Requestor City', U.name as 'Owner', U2.RealName as 'Owner RealName', Q.id as QueueID, Q.Name as 'Queue Name', T.Status, OOCF.Content as 'Priority', OCF.Content as 'Category', OCF.ObjectId, OCF.CustomField, T.Subject, TO_DAYS(IF(Resolved != '0000-00-00 00:00:00' AND Resolved != '1970-01-01 00:00:00', DATE(Resolved), CURDATE())) - TO_DAYS(DATE(T.Created)) as days_open FROM Tickets T LEFT JOIN Users U on U.id = T.Owner LEFT JOIN Users U2 on U2.id = T.Owner LEFT JOIN Queues Q on Q.id = T.Queue LEFT JOIN Users U3 on U3.id = T.Creator LEFT JOIN Users U4 on U4.id = T.Creator LEFT JOIN Users U5 on U5.id = T.Creator LEFT JOIN Users U6 on U6.id = T.Creator LEFT JOIN (ObjectCustomFieldValues OCF) on (OCF.ObjectId = T.id AND (OCF.CustomField = '3' OR OCF.CustomField = '5')) LEFT JOIN (ObjectCustomFieldValues OOCF) on (OOCF.ObjectId = T.id AND OOCF.CustomField = '4') WHERE Type = 'ticket' AND ( Status IN ('open','stalled','new','autoclose','resolved','rejected') OR ( Status = 'resolved' AND Resolved > DATE_SUB(CURDATE(), INTERVAL 30 DAY) ) ) ORDER BY T.id --end SQL-- -- View this message in context: http://requesttracker.8502.n7.nabble.com/MySQL-Question-joins-and-stuff-tp56822p56850.html Sent from the Request Tracker - User mailing list archive at Nabble.com. -- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training