SELECT ti.FIRST_NAME "Technician",
count(wo.WORKORDERID) "Total number of calls created",
count(case when std.ISPENDING='0' THEN 1 ELSE NULL END) "Total Closed calls" ,
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "No: of calls closed within SLA",
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "No: of calls closed exceeding SLA",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% of calls created and completed within SLA",
case when count(wo.workorderid) > 0 then cast(count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as float) else null end "% of calls created and completed exceeding SLA"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN workorder_queue ON workorder_queue.workorderid=wo.workorderid
LEFT JOIN queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
WHERE (wo.ISPARENT='1') AND wo.CREATEDTIME >= DATETOLONG('2021-01-13 07:00:00') AND wo.CREATEDTIME <= DATETOLONG('2021-09-13 07:00:00') AND ti.FIRST_NAME='New tech'
GROUP BY ti.FIRST_NAME
order by 1