Query to show tickets first assigned today irrespective of created date

Query to show tickets first assigned today irrespective of created date


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?
  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>