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 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 tickets that are in resolved status for more than 1 hour ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Request ID",ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID INNER JOIN Accountsitemapping asm ON wo.siteid=asm.siteid INNER JOIN ...Query to show Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT rd.REGIONNAME "Region", count(wo.workorderid) "Logged", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog" FROM ...