Project Ahead, Delay

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
               FROM(to_timestamp(max(projectdet.ACTUALENDTIME)/1000)::TIMESTAMP - to_timestamp(max(projectdet.PROJECTEDEND)/1000)::TIMESTAMP))/3600/24 "Delay Days" FROM ProjectDetails projectdet
LEFT JOIN projectdescription pd ON projectdet.projectid=pd.projectid
LEFT JOIN TaskToProjects tpr ON projectdet.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
WHERE extract(epoch
              FROM(to_timestamp((projectdet.ACTUALENDTIME)/1000)::TIMESTAMP - to_timestamp((projectdet.PROJECTEDEND)/1000)::TIMESTAMP))/3600/24 >=1
GROUP BY projectdet.PROJECTID


Ahead


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
               FROM(to_timestamp(max(projectdet.SCHEDULEDENDTIME)/1000)::TIMESTAMP - to_timestamp(max(projectdet.ACTUALENDTIME)/1000)::TIMESTAMP))/3600/24 "Ahead Days" FROM ProjectDetails projectdet
LEFT JOIN projectdescription pd ON projectdet.projectid=pd.projectid
LEFT JOIN TaskToProjects tpr ON projectdet.PROJECTID= tpr.PROJECTID
LEFT JOIN TaskDetails taskdet ON tpr.TASKID=taskdet.TASKID
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID
WHERE extract(epoch
              FROM(to_timestamp((projectdet.SCHEDULEDENDTIME)/1000)::TIMESTAMP - to_timestamp((projectdet.ACTUALENDTIME)/1000)::TIMESTAMP))/3600/24 >=1
GROUP BY projectdet.PROJECTID





      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

                    • Script to send notifications to project owner and other users regarding the project closure.

                      Tested in Latest Build: 14306 Use case: While closing a project in addition to the project owner other users who are not involved in the project can be notified using the below attached Script. Please follow the below steps to achieve the above: 1. ...

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

                    • Query to show overdue tickets with delay by days

                      Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...

                    • Script to send notifications to project owner when (Project is edited, task is added, Milestone is added)

                      Last tested in Build: 10602 1. Send mail project owner Please follow the below steps. 1.. Go to Admin --> Project Custom Functions --> Custom Actions --> Paste the content from the attachment (1. Project_sendmail.txt) and save it with a name. 2. ...

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

                      Related Products