Query to get total worklog time spent on a request. (PGSQL)
Tested in build PGSQL (14300)
Scenario:
Request1
worklog1-Total time taken to resolve=1 hr 20 mins.
worklog2- Total time taken to resolve=1hr 10mins.
While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins.
Query:
SELECT aau.FIRST_NAME AS "Requester",
max(wo.WORKORDERID) AS "Request ID",
rtdef.NAME AS "Request request type",
wo.TITLE AS "Subject",
std.STATUSNAME AS "Request Status",
ti.FIRST_NAME AS "Assigned Technician",
Longtodate(wos.ASSIGNEDTIME) AS "Request assigned time",
Longtodate(wo.RESOLVEDTIME) AS "Resolved Time",
TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' second')::interval, 'HH24:MI:SS') "Work Log 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 sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE (wo.ISPARENT='1') GROUP BY aau.FIRST_NAME,rtdef.NAME,wo.TITLE,std.STATUSNAME,ti.FIRST_NAME,wos.ASSIGNEDTIME,wo.RESOLVEDTIME
New to ADSelfService Plus?
Related Articles
Query to show total time spent for a ticket ( PGSQL)
Tested in PGSQL build (14300) Database: 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 ...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 of a technician for the current month-PGSQL
Working on Build's: 14500 Query show total time spent by technician for the current month regardless of the ticket created date based on worklogs added PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS ...Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...