Version : 10609

DB : MSSQL

OUTPUT:

SELECT "wo"."WORKORDERID" AS "Request ID",aauTech.FIRST_NAME As "First Technician", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type", "wo"."TITLE" AS "Subject", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "regionDef"."REGIONNAME" AS "Region", "sdo"."NAME" AS "Site", "dpt"."DEPTNAME" AS "Department", "sdu"."ISVIPUSER" AS "VIP User", "std"."STATUSNAME" AS "Request Status", LONGTODATE (wo.CREATEDTIME) AS "Created Time", "cri"."FIRST_NAME" AS "Created By", LONGTODATE (wo.RESOLVEDTIME) AS "Resolved Time", LONGTODATE (wo.COMPLETEDTIME) AS "Completed Time",CONVERT(varchar, ((wo.TIMESPENTONREQ)/1000) / 3600) + ':' + RIGHT('0' + CONVERT(varchar,((wo.TIMESPENTONREQ)/1000) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar, ((wo.TIMESPENTONREQ)/1000) % 60), 2) AS "Time Elapsed", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "wos"."ISOVERDUE" AS "Overdue Status", "wos"."ISFCR" AS "FCR", "wos"."REOPENED" AS "ReOpened"  FROM "WorkOrder" "wo" LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID" LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID" LEFT JOIN "SDUser" "crd" ON "wo"."CREATEDBYID"="crd"."USERID" LEFT JOIN "AaaUser" "cri" ON "crd"."USERID"="cri"."USER_ID" LEFT JOIN "SiteDefinition" "siteDef" ON "wo"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" LEFT JOIN "RegionDefinition" "regionDef" ON "siteDef"."REGIONID"="regionDef"."REGIONID" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" 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 left JOIN WO_Tech_Info wti ON woa.ASSESSMENTID=wti.ASSESSMENTID
LEFT JOIN AaaUser aauTech ON wti.NEXTTECHNICIANID = aauTech.USER_ID WHERE (wo.createdtime/1000 between datediff(s,'1970-01-01 00:00','2020-06-01 00:00') and datediff(s,'1970-01-01 00:00','2020-06-30 00:00'))  and wo.ISPARENT='1' and wti.TECHNICIANID is null