Request Management - most used request template, category and top requesters based on request count

To make any changes to these queries, refer to this post.

Report to find the most used template in the application:

SELECT serd.Name "Service Category",
       reqtl.templatename "Template name",
       count(wo.WORKORDERID) "Count of Request" FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
WHERE reqtl.isdeleted='0'
GROUP BY serd.Name,
         reqtl.templatename
ORDER BY 3 DESC

To find top used category based on request count:

SELECT serd.Name "Service Category",
       reqtl.templatename "Template name",
       count(wo.WORKORDERID) "Count of Request" FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
WHERE reqtl.isdeleted='0'
GROUP BY serd.Name,
         reqtl.templatename
ORDER BY 3 DESC

Top requesters based on request count:


SELECT aau.FIRST_NAME "Requester",
       count(wo.workorderid) "Count" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
WHERE (wo.ISPARENT='1')
GROUP BY aau.first_name
ORDER BY 2 DESC