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

[Landon Stewart]

Jason this is brilliant!  Thank you!


On 4 March 2014 13:57, j.hubbard <jason.hubbard at 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 :: lstewart at iweb.com
Lead Specialist, Abuse and Security Management
Spécialiste principal, gestion des abus et sécurité
http://iweb.com :: +1 (888) 909-4932
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140304/b855967d/attachment.htm>