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",
longtodate(projectdet.SCHEDULEDSTARTTIME) "Project Schedule start",
longtodate(projectdet.SCHEDULEDENDTIME) "Project Schedule end",
longtodate(projectdet.ACTUALSTARTTIME) "Project Actual start",
longtodate(projectdet.ACTUALENDTIME) "Project Actual end",
ProjectEstimations.ESTIMATEDCOST "Project Estimated Cost",
ProjectEstimations.tot_taskhours_cost "Project Actual Cost",
ProjectEstimations.Estimatedhours "Project Estimated hours",
ProjectEstimations.Actualtaskhours "Project Actual hours",
md.Milestoneid "Milestone ID",
md.TITLE "Milestone Title",
mdon.FIRST_NAME "Milestone Owner",
mst.StatusName "Milestone Status",
longtodate(md.SCHEDULEDSTARTTIME) "Milestone Schedule start",
longtodate(md.SCHEDULEDENDTIME) "Milestone Schedule end",
longtodate(md.ACTUALSTARTTIME) "Milestone Actual start",
longtodate(md.ACTUALENDTIME) "Milestone Actual end",
md.Estimatedhours "Milestone Estimated hours",
md.Actualtaskhours "Milestone Actual hours",
taskdet.TASKID "Task ID",
taskdet.TITLE "Task Title",
taskowner.FIRST_NAME "Task Owner",
tst.StatusName "Task Status",
taskdet.PER_OF_COMPLETION "Task Percentage Of Completion",
LONGTODATE(taskdet.SCHEDULEDSTARTTIME) "Task Scheduled Start Time",
LONGTODATE(taskdet.SCHEDULEDENDTIME) "Task Scheduled End Time",
LONGTODATE(taskdet.ACTUALSTARTTIME) "Task Actual Start Time",
LONGTODATE(taskdet.ACTUALENDTIME) "Task Actual End Time",
taskdet.Estimatedeffort "Task Estimated Minutes",
taskdet.Addtional_cost "Task Additional Costs" FROM TaskDetails taskdet
LEFT JOIN TaskToProjects projtotaskdet ON taskdet.TASKID=projtotaskdet.TASKID
LEFT JOIN ProjectDetails projectdet ON projtotaskdet.PROJECTID=projectdet.PROJECTID
LEFT JOIN TaskToProjects miletotaskdet ON taskdet.TASKID=miletotaskdet.TASKID
LEFT JOIN MileStoneDetails md ON miletotaskdet.MILESTONEID=md.MILESTONEID
LEFT JOIN SDUser projectownerdet ON projectdet.OWNERID=projectownerdet.USERID
LEFT JOIN AaaUser projectowner ON projectownerdet.USERID=projectowner.USER_ID
LEFT JOIN ProjectStatus projectstatus ON projectdet.STATUSID=projectstatus.STATUSID
LEFT JOIN SDUSer mdo ON md.OWNERID=mdo.USERID
LEFT JOIN AaaUser mdon ON mdo.USERID=mdon.USER_ID
LEFT JOIN ProjectStatus mst ON md.Statusid=mst.STATUSID
LEFT JOIN StatusDefinition tst ON taskdet.Statusid = tst.STATUSID
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID
LEFT JOIN TaskToCharge ttc ON taskdet.TASKID=ttc.TASKID
LEFT JOIN ChargesTable ct ON ttc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN ProjectEstimations ON projectdet.PROJECTID=ProjectEstimations.PROJECTID
New to ADSelfService Plus?
Related Articles
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 ...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 ...Zoho Project to SDP Integration
Requirement: Considering the earlier integration discussed in the article: https://pitstop.manageengine.com/portal/en/kb/articles/sdp-to-zoho-project-integration Subsequently, upon adding a task to Zoho Projects, it's essential to mirror this action ...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 ...