Version : 11026

DB : MSSQL

OUTPUT :      

SELECT aau.first_name "Technician",
CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/(3600*24)) AS VARCHAR(10))+'days '+CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/3600)%24 AS VARCHAR(10))+'hours '+CAST(((avg(wo.respondedtime-wo.createdtime)/1000)/60)%60 AS VARCHAR(10))+'min '+CAST((avg(wo.respondedtime-wo.createdtime)/(1000))%60 AS VARCHAR(10))+'sec'  "Avg response Time",
wof.UDF_CHAR2 "Business Relationship" FROM workorder wo
LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid
LEFT JOIN statusdefinition sdf ON wos.statusid=sdf.statusid
LEFT JOIN aaauser aau ON wos.ownerid=aau.user_id
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
WHERE wo.respondedtime !='0'  AND wo.CREATEDTIME >= <from_thisyear>
  AND wo.CREATEDTIME <= <to_thisyear>
GROUP BY aau.first_name,wof.UDF_CHAR2