| Line 159... |
Line 159... |
| 159 |
<sql>select datediff(rec.creationTimestamp, '20101231') as date_id, ag.name, rec.ticketId, IFNULL(res.ticketStatus, rec.ticketStatus) status, IFNULL(res.ticketPriority, rec.ticketPriority) priority, rec.ticketCategory category,
|
159 |
<sql>select datediff(rec.creationTimestamp, '20101231') as date_id, ag.name, rec.ticketId, IFNULL(res.ticketStatus, rec.ticketStatus) status, IFNULL(res.ticketPriority, rec.ticketPriority) priority, rec.ticketCategory category,
|
| 160 |
hour(timediff(IFNULL(res.creationTimestamp, now()), rec.creationTimestamp)) response_hours,
|
160 |
hour(timediff(IFNULL(res.creationTimestamp, now()), rec.creationTimestamp)) response_hours,
|
| 161 |
datediff(IFNULL(t.closeDate, now()), t.openDate) ticket_close_days,
|
161 |
datediff(IFNULL(t.closeDate, now()), t.openDate) ticket_close_days,
|
| 162 |
reopen.count reopen_count
|
162 |
reopen.count reopen_count
|
| 163 |
from activity rec
|
163 |
from activity rec
|
| 164 |
left join activity res on (res.ticketId = rec.ticketId and res.type = 'SEND_EMAIL_TO_CUSTOMER' and res.creationTimestamp > rec.creationTimestamp)
|
164 |
left join activity res on (res.ticketId = rec.ticketId and (res.type = 'SEND_EMAIL_TO_CUSTOMER' or res.type = 'CALLED_CUSTOMER' or res.type = 'ESCALATE_TICKET') and res.creationTimestamp > rec.creationTimestamp)
|
| 165 |
left join agent ag on (ag.id = res.creatorId)
|
165 |
inner join agent ag on (ag.id = res.creatorId)
|
| 166 |
left join ticket t on (t.id = rec.ticketId)
|
166 |
inner join ticket t on (t.id = rec.ticketId)
|
| 167 |
left join (select a.ticketId, count(*) count from activity a, activity p where a.ticketStatus = 'REOPEN' and p.id in (select max(i.id) from activity i where i.ticketId = a.ticketId and i.id < a.id) and p.ticketStatus != 'REOPEN' and p.ticketId = a.ticketId group by ticketId) reopen on (reopen.ticketId = rec.ticketId)
|
167 |
left join (select a.ticketId, count(*) count from activity a, activity p where a.ticketStatus = 'REOPEN' and p.id in (select max(i.id) from activity i where i.ticketId = a.ticketId and i.id < a.id) and p.ticketStatus != 'REOPEN' and p.ticketId = a.ticketId group by a.ticketId) reopen on (reopen.ticketId = rec.ticketId)
|
| 168 |
where (res.type = 'SEND_EMAIL_TO_CUSTOMER' or res.type is null)
|
168 |
where (res.type = 'SEND_EMAIL_TO_CUSTOMER' or res.type = 'CALLED_CUSTOMER' or res.type = 'ESCALATE_TICKET' or res.type is null)
|
| 169 |
and rec.type = 'RECEIVED_EMAIL_FROM_CUSTOMER'
|
169 |
and (rec.type = 'RECEIVED_EMAIL_FROM_CUSTOMER' or rec.type = 'RECEIVED_CALL_FROM_CUSTOMER' )
|
| 170 |
and (res.creationTimestamp = (select min(a.creationTimestamp) from activity a where a.ticketId = rec.ticketId and a.creationTimestamp > rec.creationTimestamp and a.type = 'SEND_EMAIL_TO_CUSTOMER') or res.creationTimestamp is null);</sql>
|
170 |
and (res.creationTimestamp = (select min(a.creationTimestamp) from activity a where a.ticketId = rec.ticketId and a.creationTimestamp > rec.creationTimestamp and (a.type = 'SEND_EMAIL_TO_CUSTOMER' or a.type = 'CALLED_CUSTOMER' or a.type = 'ESCALATE_TICKET')) or res.creationTimestamp is null);</sql>
|
| 171 |
<limit>0</limit>
|
171 |
<limit>0</limit>
|
| 172 |
<lookup/>
|
172 |
<lookup/>
|
| 173 |
<execute_each_row>N</execute_each_row>
|
173 |
<execute_each_row>N</execute_each_row>
|
| 174 |
<variables_active>N</variables_active>
|
174 |
<variables_active>N</variables_active>
|
| 175 |
<lazy_conversion_active>N</lazy_conversion_active>
|
175 |
<lazy_conversion_active>N</lazy_conversion_active>
|