Query on displaying columns in percentage values

Query on displaying columns in percentage values

PGSQL:

SELECT aau.first_name AS "Technician", 
       Count(wo.workorderid)                  AS "Total No. of Tickets Created", 
       Sum(CASE 
             WHEN std.internalname LIKE 'closed' THEN 1 
             ELSE 0 
           END)                               AS "Total No. of Tickets Closed", 
       Sum(CASE 
             WHEN std.internalname LIKE 'closed' THEN 1 
             ELSE 0 
           END) * 100 / Count(wo.workorderid) AS "% Tickets Completed", 
       Sum(CASE 
             WHEN std.internalname LIKE 'onhold' THEN 1 
             ELSE 0 
           END)                               AS "Total No. of Onhold Tickets", 
       Sum(CASE 
             WHEN std.internalname LIKE 'onhold' THEN 1 
             ELSE 0 
           END) * 100 / Count(wo.workorderid) AS "% of OnHold Tickets", 
       Sum(CASE 
             WHEN wos.reopened = 't' THEN 1 
             ELSE 0 
           END) * 100 / Count(wo.workorderid) AS "% of ReOpened Tickets", 
       Sum(CASE 
             WHEN std.internalname LIKE 'closed' 
                  AND wos.isoverdue = 'f' THEN 1 
             ELSE 0 
           END)                               AS 
       "Total closed tickets within SLA", 
       Sum(CASE 
             WHEN std.internalname LIKE 'closed' 
                  AND wos.isoverdue = 't' THEN 1 
             ELSE 0 
           END)                               AS 
       "Total ticket closed breaching SLA", 
       CASE 
         WHEN Sum(CASE 
                    WHEN std.internalname LIKE 'closed' THEN 1 
                    ELSE 0 
                  END) > 0 THEN Sum(CASE 
                                      WHEN std.internalname LIKE 'closed' 
                                           AND wos.isoverdue = 'f' THEN 1 
                                      ELSE 0 
                                    END) * 100 / Sum(CASE 
                                                       WHEN std.internalname 
                                                            LIKE 
                                                            'closed' THEN 1 
                                                       ELSE 0 
                                                     END) 
         ELSE 0 
       END                                    AS 
       "% of total completed tickets within SLA", 
       CASE 
         WHEN Sum(CASE 
                    WHEN std.internalname LIKE 'closed' THEN 1 
                    ELSE 0 
                  END) > 0 THEN Sum(CASE 
                                      WHEN std.internalname LIKE 'closed' 
                                           AND wos.isoverdue = 't' THEN 1 
                                      ELSE 0 
                                    END) * 100 / Sum(CASE 
                                                       WHEN std.internalname 
                                                            LIKE 
                                                            'closed' THEN 1 
                                                       ELSE 0 
                                                     END) 
         ELSE 0 
       END                                    AS 
       "% of total completed tickets breaching SLA", 
       to_char((Avg(wo.timespentonreq)/1000 || 'second' )::interval,'HH24:MI:SS')                 AS "Average Time Elapsed", 
       Sum(CASE 
             WHEN wos.isescalated = 't' THEN 1 
             ELSE 0 
           END) * 100 / Count(wo.workorderid) AS "% of tickets Escalated" FROM workorder wo 
       LEFT JOIN workorderstates wos 
              ON wo.workorderid = wos.workorderid 
       LEFT JOIN sduser sd 
              ON wos.ownerid = sd.userid 
       LEFT JOIN aaauser aau 
              ON sd.userid = aau.user_id 
       LEFT JOIN statusdefinition std 
              ON wos.statusid = std.statusid 
WHERE wo.isparent='1'
GROUP  BY 1; 

          • Related Articles

          • Additional field values

            This report helps to find the picklist values in the additional fields. First you need to find the name of the table and the column holding the data, by executing the following SDP query report:  SELECT Tablename, Columnname FROM ColumnAliases WHERE ...
          • 1. Query Basics

            Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...
          • Set Task Owner depending on the request field values

            Requirement: Set Task Owner depending on the request field values. Usecase: When a task is triggered from a request, it should be assigned to a technician depending on the request field values. Steps to follow: Goto Admin > Task Custom Functions > ...
          • Dynamic checklist based on field values

            Requirement: Based on the answers provided for resource questions / request additional fields, dynamically a checklist has to be associated to the request UseCase: When an alarm is received indicating an issue with a server’s performance, with the ...
          • Associate Checklist for a Request depending on Resource/UDF Values

            Requirement: Script to associate checklists depending on UDF Additional field values/Resource Question values. Usecase: In few organizations. they have a separate template for "New Asset Request".  Each new asset needs a different set of checklists ...