Query to show number of tickets received, pending, closed along with the count of conversations (MSSQL)
Tested in Build MSSQL (14306)
Go to Reports-New Query Report and execute this report.
SELECT datename(MONTH,dateadd(s,wo.createdtime/1000, '01-01-1970')) "Month" ,
Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) "Date",
count(wo.workorderid) "Total",
count (case when std.ispending='1' THEN 1 ELSE NULL END) "Pending Requests",
count (case when std.Ispending='0' THEN 1 ELSE NULL END) "Closed Requests",
(( select count(nw.notificationid) from Notify_WorkOrder nw left join notification no on nw.notificationid=no.notificationid left join workorder wo1 on nw.workorderid=wo1.workorderid where Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo1.CREATEDTIME/1000),'1970-01-01 00:00:00'))=Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')) ) +
(select count(co.conversationid) from conversation co left join workorder wo2 on wo2.workorderid=co.workorderid where Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo2.CREATEDTIME/1000),'1970-01-01 00:00:00'))=Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00')))) "Count conversation" FROM WorkOrder wo
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
WHERE (wo.ISPARENT='1') and
wo.CREATEDTIME >= <from_lastmonth> AND wo.CREATEDTIME <= <to_lastmonth>
GROUP BY datename(MONTH,dateadd(s,wo.createdtime/1000, '01-01-1970')),Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'))
MSSQL
New to ADSelfService Plus?
Related Articles
Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )
Tested in build PGSQL (14300) and MSSQL (14306) 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 >= ...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 ...Query to show total number of conversations, incoming and outgoing conversations (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Total Number of Conversations: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", std.STATUSNAME AS ...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 number of tickets created per template with their status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT reqtl.TEMPLATENAME "Request Template", count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE ...