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 ADManager Plus?

          New to ADSelfService Plus?

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