Query to get Group hop count of request.

Query to get Group hop count of request.

Version : 10524
DB : MSSQL


OUTPUT :





SELECT MAX("wo"."WORKORDERID") AS "Request ID",
Longtodate("wo"."CREATEDTIME") AS "Created Date",
"sdo"."NAME" AS "Site",
"qd"."QUEUENAME" AS "Current Group",
"std"."STATUSNAME" AS "Request Status",
count(wohd.columnname) "Ticket Hop Count"  FROM "WorkOrder" "wo"
LEFT JOIN "SiteDefinition" "siteDef" ON "wo"."SITEID"="siteDef"."SITEID"
LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.nextgroupid=qd2.QUEUEID
inner join workorderhistory woh on wo.workorderid = woh.workorderid
inner join workorderhistorydiff wohd on woh.historyid = wohd.historyid WHERE (wo.ISPARENT='1') group by wo.WORKORDERID  ,wo.CREATEDTIME , qd.QUEUENAME ,qd1.QUEUENAME , qd2.QUEUENAME ,std.STATUSNAME,
"qd"."QUEUENAME" ,
"sdo"."NAME" ,
"wo"."CREATEDTIME" ,
 wohd.COLUMNNAME having wohd.columnname = 'QUEUEID' order by wo.WORKORDERID

      New to ADSelfService Plus?