Query to show Time spent in each status - Time Analysis

Query to show Time spent in each status - Time Analysis

MSSQL:

SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "urgdef"."NAME" AS "Urgency", "impdef"."NAME" AS "Impact", "rtdef"."NAME" AS "Request Type", "rcode"."NAME" AS "Request Closure Code", "pd"."PRIORITYNAME" AS "Priority", "std"."STATUSNAME" AS "Request Status", "std"."ISPENDING" AS "Pending Status", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", "oboau"."FIRST_NAME" AS "OnBehalfOfUser", wof.UDF_CHAR4 "Resolution Method", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Updated Time",wof.UDF_CHAR8 "Root Cause", "wos"."ISOVERDUE" AS "Overdue Status",aaau.first_name "Technician", "ad"."ORG_NAME" AS "Account",wof.UDF_CHAR7 "Root Cause Details", LONGTODATE(wo.RESOLVEDTIME) "Resolved Time", convert(varchar(5),(sum(wo.TIMESPENTONREQ))/1000/3600)+':'+convert(varchar(5),

((sum(wo.TIMESPENTONREQ))/1000)%3600/60)+':'+convert(varchar(5),

(((sum(wo.TIMESPENTONREQ)))/1000%60)) "Time elapsed",

(select cast(sum(wsi.timespent/60) as varchar(20)) +':'+cast(sum(wsi.timespent % 60) as varchar(20)) from

wo_assessment woa1

inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid

left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%Onhold%' and wo.workorderid=woa1.workorderid) "Onhold",

(select cast(sum(wsi.timespent/60) as varchar(20)) +':'+cast(sum(wsi.timespent % 60) as varchar(20)) from

wo_assessment woa1

inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid

left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%Open%' and wo.workorderid=woa1.workorderid) "Open",

(select cast(sum(wsi.timespent/60) as varchar(20)) +':'+cast(sum(wsi.timespent % 60) as varchar(20)) from

wo_assessment woa1

inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid

left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%In Progress%' and wo.workorderid=woa1.workorderid) "In Progress",

(select cast(sum(wsi.timespent/60) as varchar(20)) +':'+cast(sum(wsi.timespent % 60) as varchar(20)) from

wo_assessment woa1

inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid

left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%Resolved%' and wo.workorderid=woa1.workorderid) "Resolved" FROM WorkOrder wo

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID

LEFT JOIN statusdefinition std ON wos.statusid=std.statusid

LEFT JOIN categorydefinition cd ON wos.categoryid=cd.categoryid

LEFT JOIN workorder_queue ON workorder_queue.workorderid=wo.workorderid left join queuedefinition ON workorder_queue.queueid=queuedefinition.queueid

LEFT JOIN aaauser aaau ON wos.ownerid=aaau.user_id left join aaauser aaau1 ON wo.requesterid=aaau1.user_id

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 "WorkOrderToDescription" "wotodesc" ON "wo"."WORKORDERID"="wotodesc"."WORKORDERID" LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "wos"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "ImpactDefinition" "impdef" ON "wos"."IMPACTID"="impdef"."IMPACTID" LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" LEFT JOIN "RequestClosureCode" "rcode" ON "wos"."CLOSURECODEID"="rcode"."CLOSURECODEID" LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" LEFT JOIN "RequestResolver" "rrr" ON "wo"."WORKORDERID"="rrr"."REQUESTID" LEFT JOIN "RequestResolution" "rrs" ON "rrr"."REQUESTID"="rrs"."REQUESTID" LEFT JOIN "SDUser" "obosdu" ON "wo"."OBOID"="obosdu"."USERID" LEFT JOIN "AaaUser" "oboau" ON "obosdu"."USERID"="oboau"."USER_ID" LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID" LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" LEFT JOIN Workorder_fields wof ON wo.workorderid=wof.workorderid

WHERE (wo.ISPARENT='1') AND wo.createdtime>=<from_lastmonth>and wo.createdtime<=<to_lastmonth>

GROUP BY wo.WORKORDERID,wo.title,queuedefinition.queuename,aaau.first_name, aaau1.FIRST_NAME,aau.first_name, dpt.DEPTNAME, cd.CATEGORYNAME, scd.NAME,urgdef.NAME, impdef.NAME, rtdef.NAME, rcode.NAME, pd.PRIORITYNAME, std.STATUSNAME, std.ISPENDING, wo.CREATEDTIME, wo.RESPONDEDTIME, oboau.FIRST_NAME, wos.LAST_TECH_UPDATE, wos.ISOVERDUE, ad.ORG_NAME, wo.RESOLVEDTIME, aaau.first_name, wof.UDF_CHAR4, wof.UDF_CHAR8,wof.UDF_CHAR7

ORDER BY 1