Query to show tickets first assigned today irrespective of created date

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

          • 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 tickets created during weekends (Saturdays and Sundays)

            MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", ...
          • Query to show ticket first assign/pick up time _ PGSQL

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",ad.org_name "Account", ...
          • 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 ...