Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Go to Reports-New Query Report and execute this query.
SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON wo.workorderid=wos.workorderid left join aaauser aaau ON wos.ownerid=aaau.user_id where wo.CREATEDTIME >= <from_today> AND wo.CREATEDTIME <= <to_today> group by aaau.first_name
UNION
SELECT 'Project' "Module" ,aaau.first_name "Technician",count(projectdet.PROJECTID) "Project count" FROM ProjectDetails projectdet left join aaauser aaau ON projectdet.ownerid=aaau.user_id where projectdet.CREATEDTIME >= <from_today> AND projectdet.CREATEDTIME <= <to_today> group by aaau.first_name
UNION
SELECT 'Problem' "Module" ,aaau.first_name "Technician",count(prob.PROBLEMID) "Problem count" FROM Problem prob left join aaauser aaau ON prob.ownerid=aaau.user_id where prob.REPORTEDTIME >= <from_today> AND prob.REPORTEDTIME <= <to_today> group by aaau.first_name
UNION
SELECT 'Change' "Module" ,aaau.first_name "Technician",count(chdt.CHANGEID) "Change count" FROM ChangeDetails chdt left join aaauser aaau ON chdt.technicianid=aaau.user_id where chdt.CREATEDTIME >= <from_today> AND chdt.CREATEDTIME <= <to_today> group by aaau.first_name
New to ADSelfService Plus?
Related Articles
Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...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 ...Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...