Problem time spent
SELECT prob.PROBLEMID "Problem ID", prob.TITLE "Title", catadef.CATEGORYNAME "Category", LONGTODATE(prob.REPORTEDTIME) "Reported Date", ownaaa.FIRST_NAME "Technician", priodef.PRIORITYNAME "Priority", statdef.STATUSNAME "Status", (chargestable.TimeSpent/1000/3600) "Time Spent", chargestable.Description "WorkLog description", pct.FIRST_NAME "WorkLog tech" FROM Problem prob LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID LEFT JOIN problemtocharge ON prob.PROBLEMID=problemtocharge.PROBLEMID LEFT JOIN ChargesTable ON problemtocharge.CHARGEID=chargestable.CHARGEID LEFT JOIN AaaUser pct ON chargestable.TechnicianID=pct.USER_ID
New to ADSelfService Plus?
Related Articles
Query to show problem time spent along with last activity on a problem (MSSQL)
Tested in MSSQL build (14306) 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 ...Query to show Problem details, timespent and its associated request IDs (MSSQL)
Tested in MSSQL build (14306) 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 ...Project Time spent
MSSQL SELECT pr.ProjectID "Project ID", pr.TITLE "Project Title", taskdet.TaskID "TaskID", taskdet.TITLE "Title", tkd.description "Task Description", taskowner.FIRST_NAME "Owner", dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ...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 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. ...