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