Query to show the number of days, the tickets are open (MSSQL)
Tested in Build MSSQL (14306)
MSSQL:
select wo.WORKORDERID"Request ID",
qd.QUEUENAME "Group",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
ti.FIRST_NAME "Technician",
sdo.NAME "Site",
LONGTODATE(wo.CREATEDTIME) "Created Time",
DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) "Days open" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
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 WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
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 SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
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 WHERE (std.ispending = '1') and wo.ISPARENT='1'
order by 8
New to ADSelfService Plus?
Related Articles
Query to show the number of days, the tickets are open_PGSQL
select wo.WORKORDERID"Request ID", qd.QUEUENAME "Group", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", sdo.NAME "Site", LONGTODATE(wo.CREATEDTIME) "Created Time", round(extract(epoch from(now()::TIMESTAMP - ...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 report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...Query to show number of requests raised per Asset (MSSQL)
Tested in Build MSSQL (14306) SELECT ci.ciname 'Asset Name', cd.COMPONENTNAME 'Product', cty.COMPONENTTYPENAME 'Product Type',COUNT(wo.ciid) 'Total Requests raised for this asset' FROM workorder wo left join CI ON ci.ciid=wo.ciid LEFT JOIN RESOURCES ...Query to show total number of calls received and resolved per month (MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created", count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END) "Closed" FROM WorkOrder wo ...