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;