Worklog time taken to resolve report
Hello!
We have an report to sum up all our worklog "time taken to resolve" from our Requests. As we use more and more parts in the ME Support plus product we need to also get the worklog "time taken to resolve" from the other parts of the product.
The current sql query:
SELECT rctd.FIRST_NAME "Time Spent Technician",cd.CATEGORYNAME "Category",ct.TIMESPENT "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID WHERE (((((((((qd.QUEUENAME = 'Automatiska ärenden') OR (qd.QUEUENAME = 'Beställningar')) OR (qd.QUEUENAME = 'Change')) OR (qd.QUEUENAME = 'Drift')) OR (qd.QUEUENAME = 'Förvaltning')) OR (qd.QUEUENAME = 'Intern support')) OR (qd.QUEUENAME = 'Säkerhet')) OR ((qd.QUEUENAME::text) IS NULL)) AND (((ct.TS_ENDTIME >= 1447974000000) AND ((ct.TS_ENDTIME != 0) AND (ct.TS_ENDTIME IS NOT NULL))) AND ((ct.TS_ENDTIME <= 1448060399000) AND (((ct.TS_ENDTIME != 0) AND (ct.TS_ENDTIME IS NOT NULL)) AND (ct.TS_ENDTIME != -1))))) AND wo.ISPARENT='1'
Our need is an query that also look into the Change, Problem and Project part of the software and get thoose numbers into the report also.
Kind regards
Jens Myretyr
We have an report to sum up all our worklog "time taken to resolve" from our Requests. As we use more and more parts in the ME Support plus product we need to also get the worklog "time taken to resolve" from the other parts of the product.
The current sql query:
SELECT rctd.FIRST_NAME "Time Spent Technician",cd.CATEGORYNAME "Category",ct.TIMESPENT "Time Spent" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID WHERE (((((((((qd.QUEUENAME = 'Automatiska ärenden') OR (qd.QUEUENAME = 'Beställningar')) OR (qd.QUEUENAME = 'Change')) OR (qd.QUEUENAME = 'Drift')) OR (qd.QUEUENAME = 'Förvaltning')) OR (qd.QUEUENAME = 'Intern support')) OR (qd.QUEUENAME = 'Säkerhet')) OR ((qd.QUEUENAME::text) IS NULL)) AND (((ct.TS_ENDTIME >= 1447974000000) AND ((ct.TS_ENDTIME != 0) AND (ct.TS_ENDTIME IS NOT NULL))) AND ((ct.TS_ENDTIME <= 1448060399000) AND (((ct.TS_ENDTIME != 0) AND (ct.TS_ENDTIME IS NOT NULL)) AND (ct.TS_ENDTIME != -1))))) AND wo.ISPARENT='1'
Our need is an query that also look into the Change, Problem and Project part of the software and get thoose numbers into the report also.
Kind regards
Jens Myretyr
Topic Participants
Jens Myretyr
ServiceDesk Plus
Eugene McNiel
wassim safar bati