Jason this is brilliant! Thank you!
On 4 March 2014 13:57, j.hubbard <jason.hubb...@circles.com> wrote: > 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 > > -- Landon Stewart :: lstew...@iweb.com Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
-- RT Training London, March 19-20 and Dallas May 20-21 http://bestpractical.com/training