| Line 155... |
Line 155... |
| 155 |
<method>none</method>
|
155 |
<method>none</method>
|
| 156 |
<schema_name/>
|
156 |
<schema_name/>
|
| 157 |
</partitioning>
|
157 |
</partitioning>
|
| 158 |
<connection>crmDC_Prod</connection>
|
158 |
<connection>crmDC_Prod</connection>
|
| 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,
|
| - |
|
162 |
reopen.count reopen_count
|
| 161 |
from activity rec
|
163 |
from activity rec
|
| 162 |
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' and res.creationTimestamp > rec.creationTimestamp)
|
| 163 |
left join agent ag on (ag.id = res.creatorId)
|
165 |
left join agent ag on (ag.id = res.creatorId)
|
| - |
|
166 |
left join ticket t on (t.id = rec.ticketId)
|
| - |
|
167 |
left join (select ticketId, count(*) count from activity where ticketStatus = 'REOPEN' group by ticketId) reopen on (reopen.ticketId = rec.ticketId)
|
| 164 |
where (res.type = 'SEND_EMAIL_TO_CUSTOMER' or res.type is null)
|
168 |
where (res.type = 'SEND_EMAIL_TO_CUSTOMER' or res.type is null)
|
| 165 |
and rec.type = 'RECEIVED_EMAIL_FROM_CUSTOMER'
|
169 |
and rec.type = 'RECEIVED_EMAIL_FROM_CUSTOMER'
|
| 166 |
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 res.creationTimestamp is null);</sql>
|
| 167 |
<limit>0</limit>
|
171 |
<limit>0</limit>
|
| 168 |
<lookup/>
|
172 |
<lookup/>
|
| Line 251... |
Line 255... |
| 251 |
<partitioning>
|
255 |
<partitioning>
|
| 252 |
<method>none</method>
|
256 |
<method>none</method>
|
| 253 |
<schema_name/>
|
257 |
<schema_name/>
|
| 254 |
</partitioning>
|
258 |
</partitioning>
|
| 255 |
<fields> <field> <name>date_id</name>
|
259 |
<fields> <field> <name>date_id</name>
|
| 256 |
<rename/>
|
260 |
<rename>date_id</rename>
|
| 257 |
<length>-2</length>
|
261 |
<length>-2</length>
|
| 258 |
<precision>-2</precision>
|
262 |
<precision>-2</precision>
|
| 259 |
</field> <field> <name>name</name>
|
263 |
</field> <field> <name>name</name>
|
| 260 |
<rename/>
|
264 |
<rename>name</rename>
|
| 261 |
<length>-2</length>
|
265 |
<length>-2</length>
|
| 262 |
<precision>-2</precision>
|
266 |
<precision>-2</precision>
|
| 263 |
</field> <field> <name>ticketId</name>
|
267 |
</field> <field> <name>ticketId</name>
|
| 264 |
<rename/>
|
268 |
<rename>ticketId</rename>
|
| 265 |
<length>-2</length>
|
269 |
<length>-2</length>
|
| 266 |
<precision>-2</precision>
|
270 |
<precision>-2</precision>
|
| 267 |
</field> <field> <name>status</name>
|
271 |
</field> <field> <name>status</name>
|
| 268 |
<rename/>
|
272 |
<rename>status</rename>
|
| 269 |
<length>-2</length>
|
273 |
<length>-2</length>
|
| 270 |
<precision>-2</precision>
|
274 |
<precision>-2</precision>
|
| 271 |
</field> <field> <name>priority</name>
|
275 |
</field> <field> <name>priority</name>
|
| 272 |
<rename/>
|
276 |
<rename>priority</rename>
|
| 273 |
<length>-2</length>
|
277 |
<length>-2</length>
|
| 274 |
<precision>-2</precision>
|
278 |
<precision>-2</precision>
|
| 275 |
</field> <field> <name>category</name>
|
279 |
</field> <field> <name>category</name>
|
| 276 |
<rename/>
|
280 |
<rename>category</rename>
|
| 277 |
<length>-2</length>
|
281 |
<length>-2</length>
|
| 278 |
<precision>-2</precision>
|
282 |
<precision>-2</precision>
|
| 279 |
</field> <field> <name>response_hours</name>
|
283 |
</field> <field> <name>response_hours</name>
|
| - |
|
284 |
<rename>response_hours</rename>
|
| 280 |
<rename/>
|
285 |
<length>-2</length>
|
| - |
|
286 |
<precision>-2</precision>
|
| - |
|
287 |
</field> <field> <name>ticket_close_days</name>
|
| - |
|
288 |
<rename>ticket_close_days</rename>
|
| - |
|
289 |
<length>-2</length>
|
| - |
|
290 |
<precision>-2</precision>
|
| - |
|
291 |
</field> <field> <name>reopen_count</name>
|
| - |
|
292 |
<rename>reopen_count</rename>
|
| 281 |
<length>-2</length>
|
293 |
<length>-2</length>
|
| 282 |
<precision>-2</precision>
|
294 |
<precision>-2</precision>
|
| 283 |
</field> <select_unspecified>N</select_unspecified>
|
295 |
</field> <select_unspecified>N</select_unspecified>
|
| 284 |
</fields> <cluster_schema/>
|
296 |
</fields> <cluster_schema/>
|
| 285 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|
297 |
<remotesteps> <input> </input> <output> </output> </remotesteps> <GUI>
|