Query to show both requests and task worklog time spent together

Query to show both requests and task worklog time spent together


DB : MSSQL

Timespent for both tasks and requests

select wo.workorderid "Request ID",
aau.FIRST_NAME "Requester",
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from chargestable ct
LEFT JOIN WorkOrderToCharge wotoc ON ct.CHARGEID=wotoc.CHARGEID 
LEFT JOIN WorkOrder wo ON wotoc.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID 
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name

Timespent on worklogs under tasks

select wo.workorderid "Request ID", 
aau.FIRST_NAME "Requester", 
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from WorkOrder wo
LEFT JOIN workordertotaskdetails wotot ON wotot.workorderid=wo.workorderid
LEFT JOIN taskdetails tdet ON wotot.taskid=tdet.taskid
LEFT JOIN tasktocharge ttoch ON ttoch.taskid=tdet.taskid
LEFT JOIN chargestable ct ON ttoch.chargeid=ct.chargeid
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID 
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name

Timespent on worklogs under requests

select wo.workorderid "Request ID", 
aau.FIRST_NAME "Requester", 
cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((sum(ct.timespent))%1000)%60) as varchar(20))+ 'Secs' "Time Spent" from WorkOrder wo
LEFT JOIN workordertocharge wotoc ON wotoc.workorderid=wo.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 SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
group by wo.workorderid,aau.first_name







          • Related Articles

          • Query to show both task comments and worklog comments

            MSSQL: SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category",  "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS "Owner", "taskdet"."TITLE" ...
          • How to add total_time_spent in worklog using API

            Please use milli-seconds as value to the attribute URL:  <URL>/api/v3/worklog?OPERATION_NAME=add&TECHNICIAN_KEY=XXXXXXXXXXXXX JSON:  input_date={ "worklog": { "request": { "id": "6" }, "description": "Adding a worklog", "technician": { "name": ...
          • Query to show total time spent for a ticket

            PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", sdo.NAME AS "Site", ...
          • Query to show total time spent of a technician for the current month_PGSQL

            Query show total time spent by technician for the current month regardless of the ticket created date. PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS ...
          • Query to show Last added worklog of a ticket _MSSQL

            MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", wotodesc.FULLDESCRIPTION AS ...