Query to show time spent added under home tasks.

Query to show time spent added under home tasks.

Please go to Reports-New Query Report and execute this query.


SELECT tk.TASKID "Task ID",MAX(tk.TITLE) "Task Title", cast((ct.TIMESPENT/1000 * interval '1 second') as varchar) "Time Spent",to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",rctd.FIRST_NAME "Time Spent Technician" FROM ChargesTable ct
LEFT JOIN TaskToCharge tkc ON ct.CHARGEID=tkc.CHARGEID
LEFT JOIN  TaskDetails tk ON tkc.TASKID=tk.TASKID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID 
LEFT JOIN queue_technician qt on qt.technicianid = rcti.userid 
left join queuedefinition q on q.queueid = qt.queueid
where module='general' AND ct.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-01-01 00:00:00') * 1000 AS BIGINT)  AND  ct.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-12-30 00:00:00') * 1000 AS BIGINT) GROUP BY tk.TASKID,rctd.FIRST_NAME,ct.TIMESPENT,ct.createdtime ORDER BY 5


PGSQL



          • Related Articles

          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • Query to show time spent by each technician in a ticket.

            Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME ...
          • Query to show Last Status Changed Time and Time spent in previous status

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician",  cd.CATEGORYNAME AS "Category",  scd.NAME AS "Subcategory",  qd.QUEUENAME AS "Group",  std.STATUSNAME "Request Status", LONGTODATE(MAX(OPERATIONTIME)) "Last ...
          • Query to show the last worklog added in a ticket

            PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show problem time spent along with last activity on a problem

            MSSQL: SELECT "prob"."PROBLEMID" AS "Problem ID",  "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status",  "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...