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

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

            PGSQL Delay SELECT projectdet.PROJECTID "Project id", max(projectdet.TITLE) "Title", max(pd.description) "Description", longtodate(max(projectdet.ACTUALSTARTTIME)) "Actual start", longtodate(max(projectdet.PROJECTEDEND)) "Projected On", extract(epoch ...