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

[edunne]

this was supposed to be an enhancement.