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?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

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

                      • Project,Milestone and Tasks.

                        DB Compatability: PgSQL and MSSQL SELECT projectdet .PROJECTID "Project Id",        projectdet.TITLE "Project Title",        projectstatus.STATUSNAME "Project Status",        projectowner.FIRST_NAME "Project Owner",       ...

                      • Time Spent on each module

                        This Report helps us to know the time spent by technicians on each module.  The Time spent report provides a step-by-step record by which time-related data can be traced to their source and provides a complete history of all Technician activities ...

                      • Time elapsed analysis

                        This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...

                      • How to Automatically Associate a Ticket to a Project using PrjID based on Ticket Template

                        This post describes the use of a sample python script you to associate a Project automatically to a ticket during its creation/edit based on conditions, such as template matching. This script can be configured under Custom Triggers and you will find ...

                        Related Products