Tested in build MSSQL (14306)
MSSQL:
SELECT wo.WORKORDERID "Request ID",
max(aau.FIRST_NAME) "Requester",
max(dpt.DEPTNAME) "Department",
max(wo.TITLE) "Subject",
max(qd.QUEUENAME)"Group",
max(ti.FIRST_NAME) "Technician",
max(std.STATUSNAME) "Request Status",
longtodate(max(wo.CREATEDTIME)) "Created Time",
longtodate(max(wo.respondedtime)) "First Responded Time",
longtodate(max(wo.RESOLVEDTIME)) "Resolved Time",
longtodate(max(woh.OPERATIONTIME)) "Assigned time",
min(aau1.FIRST_NAME) "First assigned by",
min(prev.first_name) "First Technician",
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" FROM WorkOrder wo 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 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 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 workorderhistory woh on wo.workorderid=woh.workorderid
left join workorderhistorydiff wohd on woh.historyid=wohd.historyid
LEFT JOIN aaauser prev on cast(cast(wohd.current_value as varchar) as int)=prev.user_id
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
WHERE (wo.ISPARENT='1') and wohd.columnname='ownerid' and
woh.historyid=(select max(workorderhistory.historyid) from workorderhistory left join workorderhistorydiff on workorderhistory.historyid=workorderhistorydiff.historyid where workorderhistory.workorderid=wo.workorderid and workorderhistorydiff.COLUMNNAME ='ownerid' ) and woh.OPERATIONTIME >= <from_today> AND woh.OPERATIONTIME <= <to_today> group by wo.WORKORDERID ORDER BY 6
If you want the Query to show tickets only first assigned today, refer this link.
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- 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>