Query to find the time taken to assign a field

Query to find the time taken to assign a field

Use case

The query will pull a report of when an additional field was first assigned a value

DB: MSSQL


Query 

Please replace additional field name with the Field Name Here.

SELECT "wo"."WORKORDERID" AS "Request ID", 
"ad"."ORG_NAME" AS "Account", 
"pd"."PRIORITYNAME" AS "Priority", 
"std"."STATUSNAME" AS "Request Status", 
longtodate("wo"."CREATEDTIME") AS "Created Time",  
"wo"."TITLE" AS "Subject"
convert(varchar(10),(min(woh.OPERATIONTIME)-min(wo.createdtime))/1000/3600)+':'+convert(varchar(10),((min(woh.OPERATIONTIME)-min(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),(((min(woh.OPERATIONTIME)-min(wo.createdtime)))/1000%60))  "Time taken to set Field Name Here" FROM "WorkOrder" "wo"
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID=woh.WORKORDERID 
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID=wohd.HISTORYID 
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" 
LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" 
LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID" 
LEFT JOIN "WorkOrder_Fields" "wof" ON "wo"."WORKORDERID"="wof"."WORKORDERID" 
LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID" 
LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" 
WHERE (wo.ISPARENT='1') and wohd.columnname='Field Name Here' and woh.historyid=(select min(workorderhistory.historyid) from workorderhistory left join workorderhistorydiff on workorderhistory.historyid=workorderhistorydiff.historyid where workorderhistory.workorderid=wo.workorderid and workorderhistorydiff.COLUMNNAME ='Field Name Here' ) and wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth> 


Available Date Templates

    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
    • This year - <from_thisyear> - <to_thisyear>
    • Last year - <from_lastyear> - <to_lastyear>



      New to ADSelfService Plus?

        Resources