SLA violate report not tally
Good day,
As per title subject, I have 2 report for the SLA violation, 1 is showing as a summary while the other is more detail. Sadly both of them is showing me different info.
Summary
SELECT pd.PRIORITYNAME AS "Priority",
count(wo.WORKORDERID) "Total Request",
count(case when (wos.ISOVERDUE='1') THEN 1 ELSE NULL END) "SLA violated",
count(case when (wos.ISOVERDUE='0') THEN 1 ELSE NULL END) "SLA not violated" ,
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 "% Violated",
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 "% not Violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std on wos.statusid=std.statusid
WHERE (wo.ISPARENT='1') and
wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-09-01 00:00:00') * 1000 AS BIGINT) AND
wo.completedtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-09-30 23:59:59') * 1000 AS BIGINT) GROUP BY pd.PRIORITYNAME
order by 1
Detail
SELECT longtodate(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", dpt.DEPTNAME AS "Department", lvd.LEVELNAME AS "Level", longtodate(wo.CREATEDTIME) AS "Created Time", longtodate(wo.DUEBYTIME) AS "DueBy Time", longtodate(wo.COMPLETEDTIME) AS "Completed Time", wo.TITLE AS "Subject" FROM WorkOrder wo LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN LevelDefinition lvd ON wos.LEVELID=lvd.LEVELID WHERE (wos.ISOVERDUE = true) AND wo.ISPARENT='1' and
wo.DUEBYTIME >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-09-01 00:00:00') * 1000 AS BIGINT) AND
wo.DUEBYTIME <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-09-30 23:59:59') * 1000 AS BIGINT) ORDER BY 1 NULLS FIRST