Project Time spent

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()) + (taskdet.SCHEDULEDSTARTTIME/1000), '1970-01-01 00:00:00') "Scheduled Start Time",
       dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.SCHEDULEDENDTIME/1000), '1970-01-01 00:00:00') "Scheduled End Time",
       dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.ACTUALSTARTTIME/1000), '1970-01-01 00:00:00') "Actual Start Time",
       dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (taskdet.ACTUALENDTIME/1000), '1970-01-01 00:00:00') "Actual End Time",
       taskstatus.STATUSNAME "Task Status",
       CAST(ct.TIMESPENT AS FLOAT)/1000/3600 "TimeSpent",
       tt.FIRST_NAME "Time Spent Tech" FROM ProjectDetails pr
LEFT JOIN TaskToProjects tpr ON pr.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN TaskDescription tkd ON taskdet.TASKID=tkd.TASKID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
LEFT JOIN TaskToCharge tkc ON taskdet.TASKID=tkc.TASKID
LEFT JOIN ChargesTable ct ON tkc.CHARGEID=ct.CHARGEID
LEFT JOIN AaaUser tt ON ct.TECHNICIANID=tt.USER_ID
ORDER BY 2,
         1



PGSQL


SELECT pr.TITLE "Project Title",
       taskdet.TITLE "Task Title",
       taskowner.FIRST_NAME "Task Owner",
       ct.TIMESPENT /1000/3600 "TimeSpent hrs",
       ctt.FIRST_NAME "Time Spent Tech" FROM ProjectDetails pr
LEFT JOIN TaskToProjects tpr ON pr.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN TaskToCharge tkc ON taskdet.TASKID=tkc.TASKID
LEFT JOIN ChargesTable ct ON tkc.CHARGEID=ct.CHARGEID
LEFT JOIN AaaUser ctt ON ct.TECHNICIANID=ctt.USER_ID
ORDER BY 1

                  New to ADSelfService Plus?

                    • Related Articles

                    • Project Time Spent (Query)

                      Please execute the below query to fetch project timespent. SELECT pr.PROJECTID "Project Id", pr.TITLE "Project Title", (TRUNC((sum(ct.TIMESPENT)/1000/3600))||' hr '||ROUND(((sum(ct.TIMESPENT)/1000)/60)%60)||' min') "TimeSpent" FROM ProjectDetails pr ...
                    • 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": ...
                    • 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", ...
                    • 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 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. ...