[rt-users] MySQL Question (joins and stuff)

[j.hubbard]

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.