User Management - report to technician who violated the request
This report helps to find the technician who violated the request.
SELECT wo.WORKORDERID "Request ID",
max(wo.TITLE) "Subject",
max(ti.FIRST_NAME) "Current Technician",
max(std.STATUSNAME) "Request Status",
CASE
WHEN max(tech.first_name) IS NOT NULL THEN max(tech.first_name)
ELSE max(ti.FIRST_NAME)
END "Violated by" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN
(SELECT woh1.workorderid,
ti1.first_name FROM workorderhistory woh1
LEFT JOIN workorderhistorydiff wohd1 ON woh1.historyid=wohd1.historyid
LEFT JOIN aaauser ti1 ON cast(cast(wohd1.prev_value AS varchar) AS int)=ti1.user_id
LEFT JOIN workorder wo1 ON wo1.workorderid=woh1.workorderid
WHERE (wohd1.COLUMNNAME) IN ('OWNERID')
AND woh1.operationtime > wo1.duebytime) tech ON wo.workorderid=tech.workorderid
WHERE wos.isoverdue='1'
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek> GROUP BY wo.WORKORDERID
Click this link to navigate to the next report.