VtigerTKnotUpdate.sql

select 
	concat(substr(u.first_name,1,1),substr(u.last_name,1,1)) AS tec,
	tk.status AS st, 
	max(greatest(e.createdtime, e.modifiedtime, c.dt) )AS modifiedtime,
	tk.ticketid AS ticketid,
	tk.title AS title
from 
	vtiger_troubletickets tk 
	join vtiger_crmentity e 
		on e.crmid = tk.ticketid 
	join vtiger_users u 
		on e.smownerid = u.id 
	left join comments c 
		on tk.ticketid = c.related_to
where 
	e.deleted = 0 and 
	tk.status not in ('Closed','Resolved','Completed','Canceled') 
group by 1, 2, 4
order by 1, 2, 4