Query to show resolved period for a ticket (MSSQL)
Tested in Build MSSQL (14306)
Go to Reports-New Query Report and execute the query.
SELECT wo.WORKORDERID "Request ID",
wo.title "Subject",
ti.FIRST_NAME "Technician",
std.statusname "Request Status",
case
when
DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.resolvedtime/1000),'1970-01-01 00:00:00'),GETDATE()) < 5 then 'Resolved less than 5 days'
when
(DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.resolvedtime/1000),'1970-01-01 00:00:00'),GETDATE()) >=5 and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.resolvedtime/1000),'1970-01-01 00:00:00'),GETDATE()) < 10) then 'Resolved greater then 5 days and less than 10 days'
when
(DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.resolvedtime/1000),'1970-01-01 00:00:00'),GETDATE()) >=10) then 'Resolved greater then 10 days'
else null end "Period" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
Left join statusdefinition std on wos.statusid=std.statusid
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID WHERE (wo.ISPARENT='1') and std.ispending='0' order by 3
Database : MSSQL
New to ADSelfService Plus?
Related Articles
Query to show resolved by value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...