Query to get average time spent on tickets on yearly bases.
Version : 8121
DB : PGSQL
OUTPUT :
SELECT extract( year from to_timestamp(wo.createdtime/1000)::TIMESTAMP) "Year",
TO_CHAR(((sum(rc.mm2completerequest))/count(wo.workorderid)/1000 || ' second')::interval, 'HH24:MI:SS') "Average time spent on ticket"
FROM WorkOrder wo
Inner JOIN requestcharges rc ON wo.WORKORDERID=rc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wo.ISPARENT='1') group by extract( year from to_timestamp(wo.createdtime/1000)::TIMESTAMP)
order by 1
TO_CHAR(((sum(rc.mm2completerequest))/count(wo.workorderid)/1000 || ' second')::interval, 'HH24:MI:SS') "Average time spent on ticket"
FROM WorkOrder wo
Inner JOIN requestcharges rc ON wo.WORKORDERID=rc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wo.ISPARENT='1') group by extract( year from to_timestamp(wo.createdtime/1000)::TIMESTAMP)
order by 1