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?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

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

                      • 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()) + ...

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

                      • How to automatically create a project and associate it to a change using change custom triggers.

                        This script is applicable only for builds prior to 11138. This post describes the use of a sample python script you to create a Project automatically from a Change request and create a association between them.This script can be configured under ...

                      • SDP To Zoho Project Integration

                        Requirement: When a change is moved to "Implementation" Stage, a project in Zoho Project has to be created. Generating an Auth token To use the 'Project API' we will need Project's authentication token from Zoho Accounts. Follow the instructions in ...

                        Related Products