custom query needs to be able to access ticket change table

[edunne]

Hey guys we really need to be able to do something like the following…(ditch the two custom fields time_actual and time_estimate). Basically it allows us to see all the comments made to a ticket over time and then group the results by ticket id.

SELECT id AS group, id AS ticket, summary, version, milestone, (CASE WHEN c.value ISNULL THEN ELSE c.value END) as time_est, (CASE WHEN n.value ISNULL THEN ELSE n.value END) as time_act, (CASE WHEN tc.newvalue ISNULL THEN ELSE tc.newvalue END) as comments, t.type AS type,

(CASE status WHEN 'assigned' THEN owner

' *' ELSE owner END) AS owner

FROM ticket t, enum p LEFT OUTER JOIN ticket_custom c ON

(t.id=c.ticket AND c.name='time_estimate')

LEFT OUTER JOIN ticket_custom n ON

(t.id=n.ticket AND n.name='time_actual')

LEFT OUTER JOIN ticket_change tc ON

(t.id=tc.ticket AND field = 'comment' )

WHERE status IN ('new', 'assigned', 'reopened') AND p.name = t.priority AND p.type = 'priority' ORDER BY id, milestone, t.type