Query to show first assigned and re-assigned ticket irrespective of the created date

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",

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?
  1. 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
  2. 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>