Query to show tickets first assigned today irrespective of created date
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 min(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 to show tickets that includes reassigned today, please 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>
New to ADSelfService Plus?
Resources
Related Articles
Query to show first assigned and re-assigned ticket irrespective of the created date
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", ...Query to show SLA assigned to tickets
PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates ...Query to show roles assigned to a technician with created date, status and department name
Database: MSSQL. PGSQL Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", max(sd.status) "Current Status", ...Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time_ MSSQL
MSSQL: First responded time: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", ...Query to show Created, Pending and Completed Requests
PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= <from_lastweek> AND wo.completedtime <= <to_lastweek> THEN ...