Hi,
I prepared a custom statistic report to be able to see the last 2 years of open tickets by agent but it is only showing the tickets for this year (2013).
I tried everything a know but it is not going back to display the last two years of ticket activity for open tickets by agents.
I want to try by sql box query but i am not sure what query to use.
I need the query to look for all the open tickets by agent, owner or responsible from the last two years.
Any help is welcome please and thank you!!
Mod note: Please do not post questions in HowTos
Sql box query
Moderator: crythias
-
pantered
- Znuny newbie
- Posts: 30
- Joined: 24 Apr 2013, 17:49
- Znuny Version: 3.1.3
- Location: Puerto Rico
- Contact:
Re: Sql box query
After hard work this is the result i found more convenient for my request...
Query...
SELECT t.id, t.tn ticketnumber, CONCAT(u.first_name, " ", u.last_name) name, name, t.title title, DATE_FORMAT(t.create_time, "%m/%d/%y") create_time,
case ticket_state_id
when 1 then 'New'
when 4 then 'Open'
end as TheNameYouWant
FROM ticket t
left join queue q
ON t.queue_id = q.id
left join users u
ON u.id = t.user_id
WHERE t.ticket_state_id=4
OR t.ticket_state_id=1
order by u.first_name
Hope this can help any one...
Query...
SELECT t.id, t.tn ticketnumber, CONCAT(u.first_name, " ", u.last_name) name, name, t.title title, DATE_FORMAT(t.create_time, "%m/%d/%y") create_time,
case ticket_state_id
when 1 then 'New'
when 4 then 'Open'
end as TheNameYouWant
FROM ticket t
left join queue q
ON t.queue_id = q.id
left join users u
ON u.id = t.user_id
WHERE t.ticket_state_id=4
OR t.ticket_state_id=1
order by u.first_name
Hope this can help any one...