Total time spent query report
We have a report that shows the time spent by technicians in worklogs on requests that works perfectly but I would like it to show the total time spent in ServiceDesk Plus by including worklogs for Problems and Changes too. Is this possible?
The query we have is:
SELECT rctd.FIRST_NAME 'Technician',pd.PRIORITYNAME 'Priority',rc.MM2COMPLETEREQUEST 'Time Spent',rc.DATETIME 'Cost entry date' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE (((rc.DATETIME >= <from_thisweek>) AND ((rc.DATETIME != 0) AND (rc.DATETIME IS NOT NULL))) AND ((rc.DATETIME <= <to_thisweek>) AND ((rc.DATETIME != 0) AND (rc.DATETIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 4
Regards,
UBT
The query we have is:
SELECT rctd.FIRST_NAME 'Technician',pd.PRIORITYNAME 'Priority',rc.MM2COMPLETEREQUEST 'Time Spent',rc.DATETIME 'Cost entry date' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID WHERE (((rc.DATETIME >= <from_thisweek>) AND ((rc.DATETIME != 0) AND (rc.DATETIME IS NOT NULL))) AND ((rc.DATETIME <= <to_thisweek>) AND ((rc.DATETIME != 0) AND (rc.DATETIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 4
Regards,
UBT