SELECT wo.WORKORDERID AS "Request ID", ti.FIRST_NAME AS "Technician", ad.ORG_NAME AS "Account", std1.STATUSNAME "From Status", std2.STATUSNAME "To Status",cast((wsi.timespent/60) as varchar(20)) +':'+cast((wsi.timespent % 60) as varchar(20)) "Time Spent in previous status (HH:MM)", longtodate(wsi.ENDTIME) as "Status Changed Time" 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 WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_STATUS_INFO wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN statusdefinition std1 ON wsi.statusid=std1.statusid
LEFT JOIN statusdefinition std2 ON wsi.nextstatusid=std2.statusid
WHERE((wo.ISPARENT = '1' and wsi.NEXTSTATUSID IS Not Null) AND (wo.createdtime/1000 between datediff(s,'1970-01-01 00:00','2020-01-20 00:00') and datediff(s,'1970-01-01 00:00','2021-12-31 00:00'))) ORDER BY 1,woa.ASSESSMENTID asc
SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", wotodesc.FULLDESCRIPTION AS "Description", ad.ORG_NAME AS "Account", serdef.NAME AS "Service Category",reqtemp.templatename "Template Name",std1.STATUSNAME "From Status", std2.STATUSNAME "To Status", (
wsi.TIMESPENT/60)||':'||((wsi.TIMESPENT))%60 "Time Elapsed" 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 WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
LEFT JOIN wo_assessment woa on wo.workorderid=woa.workorderid
INNER JOIN WO_STATUS_INFO wsi on woa.assessmentid=wsi.assessmentid
LEFT JOIN statusdefinition std1 ON wsi.statusid=std1.statusid
LEFT JOIN statusdefinition std2 ON wsi.nextstatusid=std2.statusid
WHERE (wo.ISPARENT='1')