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 M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • 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 ...

                    • 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", ...

                    • 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. ...

                    • 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 ...

                      Related Products